StraightPath Solutions

This topic has indeed been done to death. Yet I still often encounter unecessary database autoshrinks, scheduled shrink jobs and at times a seeming lack of knowledge on just what one should be doing as far as their database sizes.

Update [1-31-09] I wrote a follow-up to this regarding some of the questions that I see Google Search bringing folks to this blog. Check out my my additional post for some of the more common questions here .

What Happens when you Shrink a Database

When you click that shrink button (or leave a DB in autoshrink, or schedule a job to perform shrinks), you are asking SQL Server to remove the unused space from your database's files. Deallocate that space and let the O/S do what it needs with it. If you have a growing database (as the majority of non-static databases tend to be), this means that that database will grow again. Depending on your autogrowth settings (another pet peeve for another post) this growth will probably be more than necessary and you will end up shrinking again... At best this is just extra work (shrink grow/shrink grow) and the resulting file fragmentation is handled alright by your I/O subsystem. At worse this is causing file fragmentation, interrupting what would have otherwise been contigous files and potentially causing I/O related performacne problems.

What should we do then, leave free space in a file?!

Yes! In fact I find it best to plan for where your data size needs will be, not where they are at the time of initial go live. Set your initial database size to what you expect it to be in the future. If you can, look a year or more into the future. That's your initial size. Create the database that size and set the autogrowth to a reasonable number in bytes rather than percent. (I hate the default of 10%.. if you have a 1TB DB that means you will be growing 100GB anytime you need to grow a file.. Better in 2005 with Instant File Initialization but still not "responsible"). Monitor your free space and look at size trending over time so you can plan for a large allocation of more space should your planning have been off. That free space in the file is "just sitting there doing nothing" as your SAN team may say, but would you rather have what you expect to need and grow into it or scramble to allocate space at the last minute? I vote for the former and if the budget allows (if you can justify it with the growth expectations, the budget better allow it because it will need to in a year anyway) it makes sense.

Yeah, but my transaction log is always running out of space!

Yet another pet peeve and really for another post but really quick: If you are in Full Recovery Mode on a database that means you intend to be able to recover to a point in time in the event of a failure. This means you plan on using a combination of Full Backups and Transaction Log Backups (and possibly differentials). SQL Server understands your intent, and it will not truncate (free up space within the file.. notice the file stays the same, I didn't say shrink I said truncate.. Truncate frees space within a file, shrink removes that "free" space to make the physical file smaller) the log file(s) of your database (the .LDF files). Instead, they will continue to grow until such time as you take a transaction log backup. 90% of the time when I am helping someone with an out of control Transaction Log growth problem, it is because they are incurring the "cost" of Full Recovery mode (growing log file, the full logging of qualified events, etc.) but none of the benefit (being able to restore to a point in time from your transaction log backups if your log chain is unbroken and your log files survive)... Simple solution here.. Look at your backup/recovery plan!! Why aren't you taking Log backups? Is it because you don't understand them? There are plenty of resources to help. It is relatively simple to begin working on a proper backup/recovery strategy and the face you save may be your own :) Is it because you don't need point in time recovery? Well then consider Simple Recovery mode which will truncate the log at certain events. Don't go right to simple recovery mode though, analyze your situation and learn about recovery models and do what is right for your organization and business unit.

Yeah, that means you shouldn't be doing backup log with truncate_only all the time... :)

If this is all new to you.. Check out a couple of resources about these topics:

• Paul Randal has plenty to say about Shrinking Files (and says it much better than I). Check out this blog entry and look at the rest of his blog.
• Recovery Models overview - Based on SQL Server 2005 but concepts apply to most versions of SQL Server

As always, feel free to hit me with any questions specific to your environment. I am happy to exchange quick e-mails with tips/tricks especially when it comes to my "pet peeve" topics.

Related Posts

• Shrinking is a Popular Topic
• Manage your Transactions So they Don't Manage You (Part 1)
• SQLServer Pedia Article From this Blog and WebCast with Brent Ozar