Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 6 down vote accepted

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!

share|improve this answer
    
@Paul Randal: I appreciate your comment and the link to why shrink shouldn't be run unless necessary. I will tryg out the recommendation (moving files to different filegroup) and see how it turns out. –  Sung Jun 29 '09 at 15:12

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.