Yes, I realize you shouldn’t shrink your database (data files or log files), but I recently had a case where it was completely appropriate to do a shrink. We had a table that was almost a terabyte on its own. It was decided to put in an archive process that got rid of 3/4 of the table. This left us with ~750gb of empty space. The total DB size was ~1.25tb so this was going to more than cut it in half.
We needed that space for other things so it was time to shrink down the database. Now, you can work in the database while it’s being shrunk but it’s not something I would do on a busy production system. Shrink is also pretty slow. In this case, I expected it could take quite a bit of time. Maybe not days, but maybe 10-20 hours. Unfortunately, I don’t have that much time. This particular system has an outage window/slow time of a few hours a day. So what can I do?
Well, there are a few thoughts/tips.
- First of all I like to target my shrinks by using DBCC SHRINKFILE instead of DBCC SHRINKDATABASE. In this particular case, I only had one file (of 10) that needed to be shrunk. No point in hitting the entire database when a single file will do.
- Both SHRINKDATABASE and SHRINKFILE have an option TRUNCATEONLY. It’s pretty quick (a few seconds to a minute usually) and doesn’t actually move any pages around. All it does go out to the very last page in use and get rid of all of the empty space after that. So for example in my case using TRUNCATEONLY almost immediately got rid of ~200gb. It didn’t fix things but it provided some much-needed space on the drive.
- You can move tables from one filegroup to another. You could potentially move each table to a different filegroup and then truncate the file(s) in the original filegroup or even empty them out and get rid of the filegroup entirely.
- This is a very important part of the BOL entry:
You can stop DBCC SHRINKFILE operations at any point and any completed work is preserved.
You can run the shrink for an hour or two a day and eventually it will finish! This is what I ended up doing. It took a bit over 2 weeks of running the shrink every day from 1-3 hours a day but it got there.