Why is "DbccFilesCompact" status is "Suspended"?

Solution 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!

Solution 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'

---

Solution 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