Most DBAs know that shrinking a database is a bad thing. But in many cases, there is no DBA around, and either a developer or someone else in the organization is responsible for maintaining the database. And I still see from time to time one of the following two disasters:
Auto-Shrink is turned on.
There is a maintenance plan that shrinks the database once a week.
Why is shrinking a database such a bad thing?
First, it's an intensive IO operation that consumes a lot of resources and might bring your server to its knees.
Second, the shrink operation moves pages to empty slots at the beginning of the file to make free space at the end of the file. This movement of data pages creates enormous fragmentation in your indexes.
The worst thing you can do to your database is something like this:
What happens here is that the indexes have just been rebuilt with zero fragmentation, and right after that the shrink operation comes in, shuffling all the index pages and leaving them in a very bad shape again. I remember a customer telling me that they rebuild all indexes once a week on Saturday night, and every Sunday morning they find the indexes with a very high fragmentation. "Why does the rebuild operation fail?" he asked me. "It doesn't fail", I replied, "it does a great job in rebuilding your indexes, but you keep ruining them every time".
Imagine a kid building a huge Lego tower, and just when he finishes putting in the last brick, his brother comes into the room, kicks the tower, and breaks it to pieces. Then the kid's mother looks at the scattered pieces and thinks: "What's wrong with my son? Why does he fail to build a Lego tower?"
In the majority of cases, it simply doesn't make sense to shrink a database on a regular basis. Usually, databases tend to grow, so the freed space will eventually be used again anyway.
So let's summarize what we know so far – shrinking a database is a bad thing, and there is no good reason to do it regularly.
Let me say it again:
Do NOT shrink your databases!
Now, having said that, why does this option exist in SQL Server if it's such a bad thing?
The only reason to shrink a database is to free space on the disk when you really have to and you have no other choice. Other choices might be: deleting other unused files on the disk, moving other files to other disks, dropping unused databases, adding more storage, etc. If you have no other choice, and you have a lot of unallocated space in your database files, and you need extra space on the disk, then shrinking the database is probably the only thing left for you to do.
So we talked about when to shrink your database. Now, let's talk about how…
First, perform the shrink operation during a maintenance window or at least a time of low activity, because it's a heavy operation.
Second, shrink individual files rather than the entire database. You can choose between the DBCC SHRINKDATABASE command and the DBCC SHRINKFILE command. The former tries to shrink all data and log files, while the latter shrinks a specific file of your choice. There is no reason to shrink all files at once. In most cases, there is a single file that contains enough free space for what you need, and there is no reason to look for more free space elsewhere. Sometimes files are located on different disks (for example, data and log files). If you have a space issue on one disk, there is no point in shrinking a file that is located on another disk.
Another reason to use DBCC SHRINKFILE is that it allows you to specify the target size in MB, while the DBCC SHRINKDATABASE command requires you to specify the target free space as a percentage. It's more practical to calculate the target size of a file based on the amount of free space you need, compared to calculating the target percentage of free space in the whole database.
Whether you choose to shrink a single file or the entire database, you might want to try running the shrink command with the TRUNCATEONLY option first. This option causes the command to only release free pages from the end of the file(s) without moving the pages back to the beginning of the file(s). If you're lucky, and there is enough free space at the end of the file, then you might not have to perform the full shrink operation, thus saving a lot of resources and avoiding index fragmentation.
If that didn't work out, then you're left with running the full shrink operation. If you're about to free a large amount of space from the file, then you should consider doing it in small chunks (let’s say 100MB each).
There are some other things to consider, but instead of going into all the small details, we have a script for you in our Madeira Toolbox that does all the required checks and configurations, and then shrinks a file of your choice in small chunks down to a specified target size. You can download it from here.
Now, let me just make sure that you remember rule #1:
Do NOT shrink your databases!
Thank you!