I have to deal with databases that have experienced a lot of data deletion and I am preparing to move the DBs to new Sql-Server installations and additionally to a new SAN.

The size of the data files ranges between 120 and 590 GB and the quote of used space ranges between 95% on the smaller and 30% on the larger files.

Because I plan to use log shipping to do the data migration and initial restore of a full backup is required, I would like to decrease the volume to an amount that makes sense on the larger and partially empty files, knowing all the negative side effects of shrinking the files.

I tried to shrink one of the files in a live system for a very tiny amount to get an initial feeling for the required time:

The data file was 450484 MB large having 153 GB unused. I tried:

DBCC SHRINKFILE (N'logicalfilename' , 450000);

So I tried to reclaim the ridiculous amount of 484 MB. I cancelled this after 45 Minutes. Disk Waits increased during this period, mainly IO_COMPLETION and SLEEP_BPOOL_FLUSH. The command caused ~32 Mio Reads and 400,000 Writes.

Suprisingly after I cancelled it and looked at the file's size, it has been resized to the anmount of 450000 MB as desired!

I made a second try with a shrink volume of 50 MB, cancelled after 4 Minutes and once again the shrink completed successfully.

Why did the DBCC command not return? What was it waiting for? How long did it take really?

Side note, we are using FULL recovery mode and take LOG Backups in 15 min interval. Does this have some negative impact?

  • I've seen this too when we were using one of the older SQL Server versions. I've not seen it on the newer versions, but I do not shrink very often either. – Henrik Staun Poulsen Jan 11 '17 at 10:02
  • 2
    Unsure what version you're on but assuming it's on 2005 or above, have you thought about running it and seeing the percent_complete in sys.dm_exec_requests? Might give you and idea of what is going on. Also, and I'm sure you've checked for this - have you checked for locks? DBCC SHRINKFILE does actually take them as far as I know. – unclefredo Jan 11 '17 at 22:08
  • Log time after the desired shrink size has been reached dm_exec_requests still shows activity on the SPID. The command running always stays the SHRINKFILE command. Percent complete stops changing on 32,87259. WaitType is PageIOLatch_SH with a permanently changing Wait_Resource and Wait_Time. Ready and Writes also continue to change. So something is still ongoing but I still have no idea what. – Magier Jan 12 '17 at 10:55
  • Hell what, I saw that meanwhile the PERCENT_COMPLETE information showed 100 - and then started over from something close to zero..... – Magier Jan 12 '17 at 11:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.