Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Common SQL Server Mistakes - Shrinking Databases

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.

Comments

Posted by Andy Warren on 14 September 2010

I worry that we push the pendulum too far the other way, that people start to think they should NEVER shrink. In practice sometimes you archive data, sometimes the log grw because of an open trans, sometimes you need to buy space and just can't afford it. Totally agree on not doing as part of routine maintenance.

Posted by Steve Jones on 14 September 2010

I think that you should never shrink.

Until you know better. This is a rare tool that you should not look for in the top of your toolbox. More buried down below in the bottom, so you really have to look for it.

Posted by Svetlana Golovko (magasvs) on 19 September 2010

Transaction logs are different as with shrink you can actually remove fragmentation and reduce number of Virtual Log Files. See Kimberly Tripp's blog "8 Steps to better Transaction Log throughput" sqlskills.com/.../Transaction-Log.aspx.

Posted by Raj on 20 September 2010

when we should shrink databases

Posted by raje_421 on 20 September 2010

Can anyone help regarding this error :

Msg 211, Level 23, State 51, Line 1

Possible schema corruption. Run DBCC CHECKCATALOG.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Posted by Steve Jones on 20 September 2010

I think the last part of my entry is about when. It's for emergencies only.

If you have an error, please post it in the forums at www.sqlservercentral.com/forums.

Posted by Antares686 on 20 September 2010

I think a lot of people have no choice but to shrink after certain events as they just don't have the space to waste and are unable to attain additional space thru the bureaucracy in place to cut wasteful spending. Always hated when so called efficiency experts come in and tell you you can make cuts here, here and here but just base it off of their limited understanding of the needs and the paycheck they are getting. That is where I have seen some practices originate. Other practices come from a small group of people who have limited experience with the technology and instead of being fluid based on need it becomes molded in titanium (hard as he!! to alter). I do have a few rules in place myself where shrinking is used (especially of transaction logs) based on percentage of free space which I log and review for regular occurance. I cannot waste the space but if I find it is justified I can support the reason to obtain more or can determine if I can a wastefull process that if so can be retooled to improve it. I wouldn't say for emergencies only but will say limit the need and determine the factors why it is in regular use to see if you have a poor process in place.

Posted by bsheets 73864 on 20 September 2010

I disagree that it should be hard to find the command to do this.  It is very common to restore a backup of a prod database in a test environment, and change from FULL to SIMPLE recovery model.  SIMPLE typically does not need as much space for the transaction log, so you might want to shrink it immediately to save space.

Leave a Comment

Please register or log in to leave a comment.