SQL Server Log File Maintenance

Introduction

Microsoft SQL Server requires periodic maintenance. In particular, if you neglect your database log files, they will eventually consume all of the space on your disk!

Most folks use the SQL Server "Maintenance Plan" feature to create scheduled "jobs" to automatically perform the critical maintenance tasks that prevent the log files from taking over your disk.

Note: The Maintenance Plan feature is not available in the Express Editions of SQL Server... you'll have to perform similar actions manually.

Background

What is a log file?

A "transaction log file" (*.ldf) is an internal part of the database that keeps a "journal" of all database activity. Contrast that to the database file (*.mdf) that only keeps the most current information. Together the two files keep the database safe from either a server failure or from "user accidents".

Database Recovery Models

Each database operates in one of three "Recovery Models"... which are designed to allow you some flexibility on configuring the amount of disk space required to keep the database safe from disaster.

Simple - doesn't use a log file for transactions
Bulk-logged - records most transactions individually, but others in "bulk" fashion
Full (the default) - records every transaction in a log file

When to use Simple Recovery model:

Let's say you have a database where you are only concerned about the "current" version of the data. This scenario is similar to a file-system (NTBACKUP) plan... where your major concern is to quickly get the database back to a usable state... and you're not too concerned about losing the data that has occurred since the last backup.In this scenario, you'd rarely (if ever) be asked to restore the database to a particular "point in time"... so the full backups are sufficient, and you probably won't need transaction logs at all. This scenario is perfect for a developmental "play" database.

When to use Full/Bulk-logged Recovery model:

The use of the Simple Recovery model in a production database is extremely rare. The more likely scenario is that you'd be asked to restore the database to a particular point in time.For example, the Server and the Database itself are running normally, but somebody accidently deleted a table from inside a database application. That's different from a "failure recovery"... because nothing has failed! This Full recovery model requires transaction logs, so that you could restore the database to the exact instant before the "user accident" occurred. The Bulk-logged Recovery model is similar but only supports a restore to the end of the log backup (not a "point in time" between log backups)

Recovery Model summary:

Recovery Model	Requires a large transaction log file?	Restores to
Simple	No	Last database backup
Bulk-logged	Yes*	Last log backup
Full	Yes	Point in time

* Not quite as much as Full

Backup Plans:

Here is an example of a typical backup plan (with retention times) that has moderate risk (other folks do database daily and log files every 15 minutes!)

Weekly database backups, (required for archive and disaster recovery), retain for 4 weeks
Daily log backups, (required for point-in-time restores), retain for 7 days

So, this plan means you'd be able to go back for 4 weeks for archive purposes, but be able to do a point-in-time restore for only the past week. This is probably OK, since if somebody accidently messed up a query and clobbered a bunch of stuff in the database, they'd typically not wait a week to tell you about it! (and if they did, I'd have little sympathy.... oh wait, did I say that out loud?)

What makes log files grow?

By default, log files are designed to grow in size forever, until you perform a log backup.However the log backup operation itself does not physically reduce the size of the log file... instead it just "frees up" the parts of the log that's not currently in use.

After a backup, when transactions reach the physical end of the log file, they will "wrap around" to the beginning of the file to use that free space, instead of increasing the size of the file.

When there is no more free space left in the file, the log file will again grow in size. So, to prevent that from happening, you need to perform periodic log backups.

Note: You control the growth of the log file by the frequency of log backups. Consider this simplistic example: If you backup the log daily and the log file is 100 Mb, then backing up the log twice a day will allow it to be reduced to 50 Mb.

How do I backup the log files?

You'd typically perform this step from inside a Maintenance Plan:

BACKUP LOG DataBaseName TO DISK = N'<path>\DataBaseName_datetimestamp.trn' 
WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10

How do I shrink the log files?

You'd use the following command to physically shrink the log file:

DBCC SHRINKFILE (DataBaseName_Log, 0)

The first parameter is the so-called "logical name" of the log file (and not the physical name with the *.ldf extension).The second parameter (the zero), means to disregard the default "free space factor", and shrink it to the maximum extent possible.

Note: Remember, shrinking the log file is a two-step process... you cannot successfully perform a shrink operation until after you've performed a log backup!

How often should I perform a shrink?

Once you've got a good maintenance plan in place, the log file sizes will "settle down". After the first shrink operation, the a daily log/weekly database backup plan should keep the log file size from growing (much). By the end of the first week, your log files will probably stay at about the same size.

After that, about the only time you'd need to perform a log file shrink is after your database has experienced an abnormally high amount of activity (like an import job that replaces a gazillion rows).That abnormal activity will cause the log file to grow... and it will stay that size forever, until you perform another shrink operation.

A note on shrinking database/log files...You'll probably read in a lot of places that shrinking the database is not always a good idea, since it causes the database server to physically expand the files when needed. This activity degrades performance and fragments your disk space. However, that statement is really only appropriate once your database is at "steady state". I'd fully expect that you'd be doing a lot of log file shrink operations at the beginning, while you're still tweaking your backup plan.

As a general rule, you probably would not need to include a SHRINKFILE operation in a scheduled Maintenance Plan.

I tried that, and it just won't shrink!

Yes, there are times when your log file will just refuse to shrink.The error message is somewhat useless and typically says "Cannot shrink log file 2 (logfile) because all logical log files are in use"

It takes a bit of investigative work to figure out what's going on. So, issue the following command to see the current "wait state" of each log file

Use msdb
select name, log_reuse_wait, log_reuse_wait_desc from sys.databases

The most likely reason a shrink operation failed is shown in the following codes:

LOG_BACKUP - the log file has performed a "wrap around" and the free space is now in the middle of the file. Rerun a log backup so that it moves the free space towards the beginning or end of the file.
ACTIVE_TRANSACTION - there is an "open transaction" that's holding the log file hostage. Use the DBCC OPENTRAN command to find out what's going on (and perhaps kill the process).
CHECKPOINT - there has been no database checkpoint in the period of time covered by the transaction logs. Manually run the CHECKPOINT command or perform a database backup.

Take a look at the following link for a more detailed explanation of each code: Factors That Can Delay Log Truncation

Note: In some cases, you might have to perform several log backups before you can successfully shrink the log file.

I can't do a backup, 'cause I'm out of space!

There is way to just "blow away" the log file using a special type of log backup. This technique will prepare the log file to successfully shrink, but renders it useless for performing restore operations. Even though it uses the "backup" command, nothing is actually being backed up!

backup log 'DataBaseName' with truncate_only

Note: This is a drastic step, that should only be taken if you have no other choice (since you lose the ability to perform a restore for the period of time covered by that log file).

Note: SQL Server 2008 no longer supports the Truncate_Only parameter. On SQL 2008, you accomplish the same thing by changing the recovery model to Simple, doing a shrink operation, and then changing it back to "Full".

Conclusion

The bottom line is this.... you control the growth of the log file by the frequency of periodic log backups, and you control the size of the log file with a shrink operation.

End Notes

Take a look at the following for more information about creating Maintenance Plans: Creating a SQL Server Maintenance Plan

I also did not discuss the fine art of performing a point-in-time recovery. Take a look at the following: How to: Restore to a Point in Time