SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

When and How to Shrink Your Database

post thumbnail

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 in order 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:

Bad Maintenance Plan

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?"

Kid Playing with Lego

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 on a regular basis.
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 really have no other choice, and you have a lot of unallocated space in your database files, and you really 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, schedule the shrink operation to a maintenance window or at least to 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 much 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). Yossi Hakikat wrote a nice script that shrinks a file in pieces inside a loop until the file reaches the desired size. Download it here (sql file. Right-click to save).

Now, let me just make sure that you remember rule #1:

Do NOT shrink your databases!

Thank you!

Photos courtesy of Indiana Public Media and Phyllis Buchanan

Guy Glantser

Guy Glantser, Data Platform MVP, is the leader of the Israeli PASS chapter and also the CEO and founder of Madeira Data Solutions. His career has been focused on the Microsoft Data Platform for the past 20 years, performing various database roles as either an on-site DBA, an external consultant or a speaker. Guy is involved in many activities in the Microsoft Data Platform community. He occasionally speaks at community events, such as PASS Summit, SQLBits, SQL Saturdays and user groups around the world. He also co-hosts the SQL Server Radio podcast.

Comments

Leave a comment on the original post [www.madeira.co.il, opens in a new window]

Loading comments...