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.The process SQL uses is ugly and results in Index fragmentation that affects performance in the long run. 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 that index fragmentation I mentioned, file fragmentation, interrupting what would have otherwise been contiguous files and potentially causing I/O related performance problem
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 1 TB DB that means you will be growing 100 GBs 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.
More Coding 