Shrinking Databases

From SQLServerPedia

Jump to: navigation, search

Contents

Don't Touch that Shrink Button!

Many of you often encounter unnecessary database autoshrinks and scheduled shrink jobs. This article offers insight on what you should be doing about your database sizes.

Backup Log with Truncate_Only Tutorial Video

Here's a short three-minute video explaining why you might not want to use the BACKUP LOG WITH TRUNCATE_ONLY command to free up space on your full disk drives:

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090406-TruncateLogs.flv

For more tutorial videos like this, check out the SQL Server Tutorials page.

What Happens When You Shrink a Database?

When you click that shrink button (or leave a database 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 do, there’s a good chance that your database will continue to grow (as the majority of non-static databases tend to do).

Depending on your autogrowth settings, this growth will probably be necessary and you will end up shrinking it again. At best, this is just extra work (shrink grow/shrink grow), and the resulting file fragmentation can be handled by your I/O subsystem. At worst, this is causing file fragmentation, interrupting what would have otherwise been contiguous files and potentially causing I/O-related performance problems.

Allocate More Space than Necessary

Determine what your future data size needs will be, not where they are at the time of initial go live. Based on your needs, create the database size and set the autogrowth to a reasonable number in bytes rather than by a percentage. You should monitor your free space and look at size trending over time to plan for a larger allocation of space if your planning is off. Your SAN teams may indicate that the free space in the file is just sitting there doing nothing, but you are better off having that extra space instead of scrambling to allocate space at the last minute.

Don’t Run Out of Space on Your Transaction Log

If you are in a full recovery mode on a database, that means you intend to recover to a point in time in the event of a failure. It also 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 the log file(s) of your database (the .LDF files). Instead, the files will continue to grow until you do a transaction log backup.

When you help people with an out of control transaction log growth problem, they are most likely incurring the cost of full recovery mode (with a growing log file, the full logging of qualified events, etc.) but gain none of the benefit. The simple solution would be to look at your backup/recovery plan. If you aren't doing log backups or you just 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 avoid future problems. If you don't need point-in-time recovery, you should consider simple recovery mode which will truncate the log at certain events. However, do not go straight to simple recovery mode. You should analyze your situation and learn about recovery models to do what is right for your organization.

Contain Your Transaction Log

If your transaction log is growing out of control, there is a strong possibility that you are in full recovery mode and you are not backing up your log file on a regular basis. Your transaction log then continues to grow until you deliberately back it up (a full backup won't do). This is the expected result since full recovery mode means you want the ability to back up to a point in time. As long as your backup is someplace safe, it will limit your losses according to the frequency of your backups.

Solutions:

  • Set up a log backup schedule that meets your business needs. Search books online and understand recovery models. Also, figure out the SLAs you are supposed to be supporting. Once you figure out your SLAs, get your logs backed up on the same schedule. Make sure the backups are going to a handle more than your mdf/ldf files so they are useful in the event of a failure. You could even send them to tape directly or after a copy. You should be able to see your log files become more manageable size-wise.
  • Get more space. Maybe you are doing log backups but you still don't have enough space. Either your activity is quite high or your allocated space is quite low. If it's the former, try log backups more frequently. If it's the latter and the former, more space for your log files may be required.
  • Switch to simple recovery mode. This is not to be done lightly. You are no longer able to restore to a point in time, but you can only restore to the last full backup. If this in line with your SLA and you have no desire to restore to a point in time, switch to this mode. Your log file will now truncate (see below) at certain intervals.
  • Look at your growth ratio while you are adding that space or setting up your backup. Is it the default 10% for a transaction log? How large is your log file? Is 10% really the right increment you want to see it growing by? On that same note, has your log file grown a lot larger than it need be because of poor management? Perhaps once you do your first T-Log backup, you should look at setting a reasonable size knowing that it will be truncated on a regular basis. If that is considerably smaller than where you are, you should try one last shrinking of the log file.

The Difference Between Truncate and Shrink

There’s a lot of confusion surrounding the difference between truncate and shrink. You may have truncated your log file but you still have no free space and the file hasn't reduced its footprint at all. This is because a truncation does nothing to the physical size of the allocated file on the O/S. A shrink operation actually clears space from a file and a truncate essentially frees up the used space within that file. This is why a shrinking of a log file that is using all of the space won't affect the size and why a truncationof a log file won't reduce the size. A truncation would have to happen first to make room available for the shrink to work. This is not recommended, however.

Bad Advice to Avoid

Just delete the log file

This advice takes can take the form of advice like, "Just stop SQL detach your database delete the log file and reattach without log." This will definitely remove any transactions in your log, and possibly leave your database in a transactionally inconsistent state, meaning there’s potential for loss of data or worse. If you are stuck without space for future growth, try the log backup. If you must do one last truncation and shrinking after making a full backup, heed the warning to get a real recovery strategy in place.

Setup a nightly job to issue BACKUP LOG WITH TRUNCATE_ONLY

This will remove all transactions from the log. Unfortunately, this will also remove your ability to recover those transactions should your database run into problems. Keep in mind that this command is also deprecated in coming versions of SQL Server.

Author Credits

Mike Walsh

This wiki article was adapted from a series of blog posts by Mike Walsh.

Mike is an experienced SQL Server professional who has worked almost exclusively with SQL Server in various capacities for nearly ten years. He has fulfilled the roles of DBA, developer, business analyst and performance team lead but he always works his DBA experience into each role. Most recently he is the Principal Database Administrator and SQL Server subject matter expert for a global insurance company. He also assists organizations with SQL problems through his consulting firm, StraightPath Solutions.

His online presences include:

Related Reading

For more information about shrinking databases, check out these articles:

Retrieved from "http://sqlserverpedia.com/wiki/Shrinking_Databases"