I have a production SQL Server 2008 with 8 TB spread over several databases. I've deleted about 85% of the data in each database and MUST recover the disk space. The final SUM of all USED_SPACE will be 1.2 TB.
This SHRINKFILE process must fit within a 7 hour maintenance window PER database.
However the DBCC SHRINKFILE on each is taking MANY hours to run. For example shrinking an 800 GB database down to 120 GB takes over 15 hours.
Unfortunately that exceeds my maintenance window and I'm forced to kill the process hoping the database does not become corrupted (subsequent DBCC CHECKDB shows they are fine).
I can see the DBCC SHRINKFILE does NOT fully utilize the available Disk I/O nor CPU resources.
For example, it is possible to copy the full sized database files from one disk to another within only a few hours, but the SHRINKFILE process takes considerably longer.
NOTE: the databases are all set to SIMPLE recovery mode and AUTO_SHRINK is off.
Yes, I see MANY people say to never do this as it destroys indexes – I do plan to rebuild the indexes when the SHRINKFILE completes.
Is there any way to increase the priority of the SHRINKFILE commands or any alternate solutions? Here are some options I'm considering:
- SET DEADLOCK_PRIORITY HIGH in the same session before running the
SHRINKFILE.
(But I doubt this will help.) - Creating a new database and copying all the data table-by-table.
Best Answer
The solution is to Leave extra room.
When shrinking the MDF files, if I leave some unused space (do not remove ALL unused space) then it finishes considerably faster.
For example: If a 3TB database has only 200MB used, shrinking it to 200MB would take a very long time. However if I shrink it to 300MB then the shrink process is much faster.
I'm guessing SQL must defragment the data to the extreme in order to remove ALL unused space. However by leaving extra room it must only reorganize larger sections of data or otherwise adjust the file pointers to fit.