When and How to Shrink Your Database

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:

  1. Auto-Shrink is turned on.

  2. 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.