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?