Why is "DbccFilesCompact" status is "Suspended"?

Question

I am running SHRINK file on a 600G data file.

Currently, status is reported as "suspended" and sys.dm_exec_requests.percent_complete for DbccFilesCompact command reports that it is running (but very slowly)

Is there a way to check why it's being suspended and how to make it run smoother?

FYI - SQL Query for Checking Status

select T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
       , R.cpu_time, R.total_elapsed_time, R.percent_complete
from   sys.dm_exec_requests R
       cross apply sys.dm_exec_sql_text(R.sql_handle) T
order by Command

Answer 1

No, you can't check why it's running slowly, but I can give you some hints:

1) In SQL 2005, the management of nonclustered indexes changed from the Storage Engine (my team) to the Query Processor. This has many side-effects, one of which is the speed with which heap data pages can be moved by shrink. All nonclustered index records contain a backlink to the data record they are indexing - in the case of a heap, this is a physical link to a record number on a specific data page. When a heap data page is moved by shrink, all the nonclustered index records that backlink to records on that page must be updated with the new location of the page. In 2000 this was done very efficiently by the Storage Engine itself. In 2005 onwards, this must be done by calling the Query Processor to update the nonclustered index records. This is sometimes up to 100 times slower than in 2000.

2) Off-row LOB values (either actual LOB data-types or row-overflow data) do not contain a backlink to the data or index record they are part of. When a page of LOB records are moved, the entire table or index they are part of must be scanned to figure out which data/index record points to them, so they can be updated with the new location. This is also very, very slow.

3) There may be another process using the database that is causing the shrink to block waiting for the locks it needs to move pages around.

4) You may have snapshot isolation enabled, and shrink cannot move pages with version store links until the transactions requiring those older versions have completed.

5) Your I/O subsystem may be underpowered. A disk queue length higher than low single digits means your I/O subsystem in the bottleneck.

Any or all of these could be contributing to slow run-times of shrink.

In general though, you don't want to run shrink. See this blog post for details: Why you should not shrink your data files.

Hope this helps!

Answer 2

You may run this script to check the percentage completed!

SELECT 
    percent_complete, 
    start_time, 
    status, 
    command, 
    estimated_completion_time, 
    cpu_time, 
    total_elapsed_time
    --,*
FROM 
    sys.dm_exec_requests
WHERE
    command = 'DbccFilesCompact'

Answer 3

I'm shrinking a database in SQL Server 2008 SP1 and one way i can tell the progress of the Shrink command is by executing sp_lock spid and for the most part I can see that it puts a lock on file 1 then when done it places a lock on file id 2, and so on and this way i can tell when it is working on the last file id and this is my indication that is is almost complete.

Thanks,

Alex Aguilar

Answer 4

I discovered what was the problem (in my case) and I offer here the solution I used.

I had nothing using the database, and master was the default database on my session, I have verified that using sp_who2. Then I right clicked on the database, select "tasks" and then "shrink" and on "ok" the dialog. Cheking again with sp_who2, the status is "suspended" by several minutes and after that aborted becouse "no exclusive lock can be obtained". Guess yourself, but I am sure that the dialog itself is the one that causes that.

So I decided to go via command line using:

DBCC SHRINKDATABASE(myDataBase)

(Witch is everywhere documented), Then the shrink took just a few seconds.