4

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.
Improve this question
4
  • 1
    Have you tried it in many chunks (say, 10 GB or 20 GB at a time) instead of trying to go from 800 GB -> 120 GB in one single command? Also, after you perform this shrink, will you never add more data to the database? All that space you freed has to be used for that. Also, rebuilding your indexes after your shrink operation, will also grow the file again and take up more of that precious space you just freed up.
    – Aaron Bertrand
    Dec 4, 2018 at 5:21
  • Actually I did try that, it's how I learned about my solution below. Yes, I will be adding some data back in - however not at the same growth rate as before (See my comment below regarding switching from tiff to pdfs.) Typically I do not shrink databases as the space will eventually be used again, but in this case I was able to save our company money going from 8TB of data over 10 year period down to 1.2TB by only changing the format of the scanned documents. There is no indexes changed significantly from this conversion as I was not indexing the binary data.
    – Moon
    Dec 4, 2018 at 7:16
  • Well, just to play devil’s advocate, how much did you save and how much of your time did you spend solving the problem? Companies rarely look at it this way but that is a bunch of hours they won’t get back, that you could have spent doing something more productive. :-) And your bill will creep up again (immediately when you rebuild he indexes, and then more gradually as you add more data).
    – Aaron Bertrand
    Dec 4, 2018 at 14:07
  • It took about a week for me to develop the JPG-PDF's. But it solved the rapid database growth issue for over 1200 clients spread across the country. All their databases were growing fast due to the Multi-page TIFF's causing them all to spend more money on larger drives and also their backup solutions to keep up. With this one week of work from me, not only was their current database sizes reduced but it also reduced the growth rate by the same ratio. Now all the servers that were handling the larger DB's are now about to provide years more service without the cost of additional upgrades.
    – Moon
    Dec 4, 2018 at 21:01

1 Answer 1

Reset to default
5

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.

Improve this answer
4
  • 1
    Whilst this is an interesting answer, I wonder if there's any numbers you could back this up with? Who's to say the storage wasn't underutilised during the shrink etc? How do you know it's definitely the size that changed the timings?
    – George.Palacios
    Dec 3, 2018 at 16:27
  • 1
    I agree with Don's answer, I have done many times in the past,after some maintenance I'm left with huge datafiles. What i have done is to create a cursor for a dbcc shrink with 100 MB everytime, this way if my maintenance window is over, I can simply stop the script and some work is already done. I'd suggest you to test is on a non prod environment you will see the difference.
    – dbamex
    Dec 3, 2018 at 22:42
  • Maybe the better solution is to buy more disk so you're not playing hot potato with free space.
    – Aaron Bertrand
    Dec 4, 2018 at 5:22
  • 1
    Disk space was not a major concern, however we were paying for every GB to our datacenter hosting company. They have a very large and fast SAN environment. In the original databases there were many TB of binary objects (Scanned documents in multipage Tiff form.) in my recent code update I converted those to JPG pages within PDF files, which made about a 10 to 1 reduction in size. Combined with other data the DBs went from 8TB down to 1.2TB. By recovering the space we were able to save money every month on our datacenter bill.
    – Moon
    Dec 4, 2018 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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