I don’t like there being an easy command to shrink databases, and I especially don’t like seeing the shrink option as a part of the default maintenance plans.

However it seems that this technique for managing sizes is used quite often, and even given as advice by some people. A few comments about this feature:

First, don’t regularly shrink databases. Actually, don’t shrink databases at all if you don’t understand what it does. Paul Randal, who managed the storage engine team, wrote a blog about why not: Here’s a good reason not to run SHRINKDATABASE. The bottom line is that this fragments your indexes, which raises reads and decreases performance.

If you are concerned about space usage, you have two choices: add less data or buy more space.

SQL Server database files aren’t like a Word or Excel file. They don’t allocate space on disk as it’s needed. Well, they do if you have autogrow turned on, but really the files and server expect to have free space in the data files for data growth, change to data (and potential page splits/new extent allocations), and for maintenance.

If you rebuild indexes regularly, and you ought to if they become fragmented, you need free space in your server. An index rebuild copies the entire index to a new, un-fragmented set of pages, and then drops the old index. So you need double your disk space for rebuilds.

Managing space proactively is something you should do, and that means that you want to leave a pad inside your data files to allow for data growth. If you don’t have enough disk space, buy more. You need the space for data, and for performance.

Transaction Log Files

Now the transaction log files are a slightly different story. You still want to size them correctly, and some good reasons from Mr. Randal on this. You should set your log file size based on the frequency of your backups. The backups are scheduled based on your risk tolerance. Basically, more frequent backups, less transaction log space needed.

However regularly shrinking your log files doesn’t introduce fragmentation, but it is dumb. Maybe not dumb, but it’s a waste of resources. Your server needs a t-log file size of xx to handle the regular activity on your server. Shrinking it at night and having it grow the next day to handle load is silly. And a waste of disk writes.

Set your log file, manage it as needed, don’t shrink it.

When to Shrink

So should you never shrink? No, you can shrink, but the feature there is for emergencies or one-time events. If I get a load of 500GB on my 1TB data once a year, I might get crazy log growth. I might plan for that by expanding my log in advance, and then shrinking the log afterward, back to the size that I normally use.

The same thing could occur in a database. Perhaps you move some data to a read only db and want to get the space down to data + largest index. Then you can shrink, rebuild indexes, and leave the log there. You can’t shrink to just data without fragmenting, so don’t try.

When you shrink, use SHRINKFILE, and target specific files, for a specific reason. Not as part of regular maintenance.