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.