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 in the post, “Shrinking Databases is a Popular Topic“.
Updated [11/16/13] This post continues to be a popular destination for searches like “How do I shrink a database?” and “Shrink SQL Server Database” – I wanted to edit the flow a bit, add answers to those questions and reiterate a default position – It isn’t typically a good idea to shrink your database. There are exceptions, but they are exceptions. If you’ve come here looking to just shrink stuff and think that will solve all potential problems in your SQL Server database, please read the post and reconsider.
This post is part 1 in a series on shrinking databases in SQL Server and transaction log management. You can see them all in the Shrinking & Transactions category where I have a few links about SQL.
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. In fact, as a SQL Server consultant with Linchpin People, this is still one of the common findings when we do a Database Wellness exam – even in 2013 – either shrinks happening, or poor transaction log management. In fact if you find yourself wondering what is best here, you might consider a WellDBA™ Exam with us. You can request info on those from us.
What Happens when you Shrink a Database?
When you click that shrink database 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.The process SQL uses is ugly and results in Index fragmentation that affects performance in the long run. You’ve deallocated that space and are letting the O/S do what it needs to with it. If you have a growing database (as the majority of production 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 that index fragmentation I mentioned, file fragmentation, interrupting what would have otherwise been contiguous files and potentially causing I/O related performance problems. Really though, you are wasting time and introducing index fragmentation.
What should we do then, leave free space in a SQL Server data 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 I talk about this more in other posts in this category and in a Question and Answer I did on log growth for DBA.StackExchange. For here though suffice it to say:
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 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 backup and recovery needs.
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 it and future versions of SQL Server up to 2012, plus you can see the version you need.
As always, feel free to e-mail 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.