Updated [2016] This post continues to be a popular destination for searches like “How do I shrink a database?” I made some copy edits. I also wanted to reiterate a default position: It isn’t usually a good idea to shrink your SQL Server database. There are exceptions, but they are exceptions.
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. As the image above indicates, this isn’t a “DON’T!” it’s more of a “Stop and Think! Slowdown and understand the road ahead”.
This topic has indeed been done to death. But as a SQL Server consultant, unnecessary shrinking or recovery confusion are common findings we find in a SQL Server Health Assessment, even in 2016. If you have misses here? It’s our experience you’ll have them elsewhere.
What Happens when you Shrink a Database?
When you shrink a database, you are asking SQL Server to remove the unused space from your database’s files. The process SQL uses can be ugly and result in Index fragmentation. This fragmentation affects performance in the long run. You’ve freed that space and are letting the O/S do what it needs to with it, so you got what you asked for at least. If you have a growing database, this means that database will grow again. Depending on your autogrowth settings, 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. At worse this is causing index fragmentation, file fragmentation, and potentially causing performance problems during the shrink.
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 for this future 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 SQL Server 2005 and up 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 be off. That free space in the file is “just sitting there doing nothing” as your SAN team may say, but I’d rather have the space ready for the future already.
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.
Related Posts
