This post is part 1 in a series on shrinking and transaction management. Part 2 is linked at the bottom or here. You can see them all in the Shrinking & Transactions category.
This topic has indeed been done to death. Yet I still often encounter unnecessary 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 contiguous files and potentially causing I/O related performance 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
I know this comment comes late in the game, but I have a question that you may be able to answer:
I was under the impression that one of the reasons to shrink a database was to defragment the database files, something that can have a positive effect on transaction heavy databases (or so Oracle would like me to believe).You seem to be saying this is true:
I’m not clear what the difference is. Do you mean that the OS (or a commercial disk defragger for that matter) will handle defragmentation adequately without worrying about the internal shrink function? Another way of saying this: If file level fragmentation is an issue for an organization, simply running Windows deframentation tools will address this issue regardless of what’s done in SQL Server and you don’t need to run a separate routine for this purpose, unlike, say Oracle database?
Thanks
Hey Barry
I apologize for any confusion I may have caused with my stream of thought style and wording. I can’t speak towards Oracle as I am not as familiar with the physical file organization or the indexing structures in Oracle databases.
For SQL Server the major point I was trying to make, however, was that physical file fragmentation is bad. The Best case is that this fragmentation is handled somewhat alright by the O/S and its defragmentation processes and doesn’t end up a big deal. That was slightly tongue-in-cheek. The more likely case is closer to the worst case: the physical fragmentation will cause pain, the cost of the growths will cause pain and it is sort of all for not when you could have right sized the database from the beginning for a value that you can grow into over time.
Your first thought about reorganizing the database may be induced by some of the wording in SQL Server 2000 maintenance plans which sort of lump an index reorganization and shrink into what seemed like one operation (IIRC).
There are really two main kinds of fragmentation to think about here: Physical File Fragmentation (you want to prevent this by trying to minimize growths and don’t shrink your production database) and index fragmentation (which comes in two forms.. One form being your index pages no longer physically in the logical order of the doubly linked list and the other form being 8KB data pages, due to page splits from inserts/updates and due to delete activities, no longer being as full as they should be). The best way to handle this type of fragmentation is through rebuilding or reorganizing indexes, not shrinking a database.
To be honest, I can’t find any positive benefit from shrinking a production database. All that is designed to do is reduce the free space within your database files. Yes there is an option during a shrink that can reorganize data as best as possible to determine where that free space comes from but this doesn’t mean deal with index fragmentation.
What would webmasters do if database size is limited in hosting services? For example: 120MB before shrinking and 18MB after.
I think Brent’s answer on his blog for this same question is the right one: Properly size your database in the first place with the amount you need, room for growth and monitor it closely over time. Shouldn’t have a lot of growth and shrink if you set it up right from the start.
[...] – nowhere in today’s discussion am I going to shrink the databases. Shrinking databases is evil. Instead, we’re going to do some things to lose the fat and keep the [...]