The Database Transaction Log - Part 1: Managing Size

This post will be the start of a series on the Database Transaction Log.  This started out as a single posting, but the topic is vast that there is no way to properly cover it in a single post.  I could probably write a mini pamphlet on the transaction log in SQL Server the topic is just that big.  I plan to focus on common problems that I consistently see on the MSDN Forums regarding the transaction log, as well as how to prevent/correct them.

The transaction log in SQL Server is one of the most important parts of a SQL Server database, as well as one of the most common generators of problems I see online.  If you don't believe me, do a Google, MSN, or Yahoo search for 'transaction log full', and you will find article after article and question after question dealing with the subject.  Worst yet are the stories of a deleted transaction log:

http://www.sqlservercentral.com/articles/Disaster+Recovery/63311/
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2727860&SiteID=1
http://bytes.com/forum/thread543465.html

To start with let me first say that you should never, ever, never, under any circumstances delete your database log file.  This is, as Jens Suessmeyer put it, "the transactional heart of the database." 

In a managed database server the database data/log files should only grow when specified by a Database Administrator.  This is not to say that you should turn AutoGrowth OFF on the databases in your environment.  I prefer that this option is left ON as a sort of insurance policy for your database in the event that the space available drops fast enough that you can't react in time to prevent a problem.  It should however, be configured to grow by a fixed size rather than a percentage.  The reason for this is that a fixed size has a predictable cost should the database have to grow automatically whereas a percentage size will be more and more expensive the larger the database grows to. 

I previously blogged about the How to Automate Common DBA Tasks series that I posted on the MSDN Forums Example Site.  One of the articles included in that series is a Data File Free Space monitoring script.  This can be used to monitor the available free space in the log files, and provide early notification to an administrator that they need to grow the file.  By manually managing the size of the log file, an administrator will know if there is a problem almost immediately.  The transaction log on an established database should very rarely have to be grown once properly sized.

So what exactly will cause the log file to fill up and need to be grown?  There are a number of things that can cause this:

1. Full Recovery Model without Log Backups
2. Long running transaction
3. Uncommitted transaction
4. Rebuilding Indexes
5. Loading Bulk Data without minimizing logging.

The first item listed is the primary reason that log files get to be oversized, and eventually consume all of the available disk space on the server.  I have yet to figure out why or how, but almost every time that someone posts a problem with an oversized transaction log, the database is in FULL Recovery, and the only backups being performed are nightly full backups.  If you are not backing up the transaction log between full backups, then the database should be in SIMPLE recovery.  The reason for this is that the transaction log is not truncated in FULL recovery except by log backups using the BACKUP LOG command, and specifying WITH TRUNCATE_ONLY doesn't make sense.  If you aren't going to backup the logged data for point in time recovery purposes, then there is no reason to not have it auto truncate on checkpoint.

Long running transactions most often associated with a growing or oversized transaction log are generally data purging processes that are running as a single transaction like the following:

DELETE 
FROM MYTABLE
WHERE MYCOL < @Criteria

Purging data like this will be heavily logged, even on databases in SIMPLE recovery.  The reason being that there is no commit between the start and commit, so if one million rows are being deleted, then the log has to hold all one million deletes to be able to perform a rollback of the operation.  The appropriate way to purge data like this was provided by fellow forum member Adam Haines in response to numerous forums postings:

DECLARE @BatchSize INT,
        @Criteria DATETIME
SET @BatchSize = 1000
SET @Criteria = '1/1/2005'

WHILE EXISTS(SELECT 1 FROM MYTABLE WHERE MYCOL < @Criteria)
BEGIN
    DELETE TOP (@BatchSize)
    FROM MYTABLE
    WHERE MYCOL < @Criteria 
END

Another solution to this problem was provided by Denis the SQL Menace is:

DECLARE @BatchSize INT,
        @Criteria DATETIME,
        @RowCount INT
SET @BatchSize = 1000
SET @Criteria = '20050101'
SET @RowCount = 1000
 
SET ROWCOUNT @BatchSize
 
WHILE @RowCount > 0
BEGIN
    DELETE 
    FROM MYTABLE
    WHERE MYCOL < @Criteria

    SELECT @RowCount = @@rowcount 
END
 
SET ROWCOUNT 0

Both of these solve the problem by working in smaller implicit transactions which will perform much better, as well as control the size of the transaction log. 

Uncommitted/Open transactions are problematic beyond just causing transaction log growth.  An open transaction will also cause excessive blocking in the database which will also impact the application users.  To find open transactions in a database, you can use DBCC OPENTRAN, which will return the active transactions in the current database.  To demonstrate this, run the following statement in one window:

use tempdb
go
create table mytable
(rowid int identity primary key)

begin transaction
insert into mytable default values

Then open a new window and run:

dbcc opentran

The output from DBCC OPENTRAN will show the open transaction and offending SPID in the database as follows:

Transaction information for database 'tempdb'.

Oldest active transaction:
    SPID (server process ID): 55
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (20:44:214)
    Start time    : Nov 13 2008 10:35:09:780PM
    SID           : 0x01050000000000051500000064116832e36ccd723422e75bba640000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To investigate this further, you can use DBCC INPUTBUFFER(spid#) to get the last command run on the offending SPID.  From there, you can determine what action you want to take, whether to kill the offending SPID and cause it to rollback, or maybe troubleshoot further to identify what caused the transaction to be left open.

Rebuilding Indexes and bulk loading data should both be done in SIMPLE or BULK_LOGGED recovery.  The reason for this is to minimize the logging that is done, but you can't eliminate it completely.  In my experience, rebuilding indexes can require a transaction log to be larger than the size of your largest index.  This is where sizing your log file is important as a part of future capacity planning, and should be done considering how large the database might be in the future.

If you are reading this because you are already in trouble, there are a number of ways to deal with a oversized transaction log, and none of them necessitates deleting the file from the file system.  If you get yourself to the point that your server is completely out of disk space, there is no network path where you can backup your log to, and the log is in the hundreds of GB in size, then the best recommendation is to put the database into Simple recovery, which will truncate the active portion of the log and allow it to be shrunk.  I would highly caution and recommend that a new Full backup of your database be taken immediately after performing such an operation.

Look for further postings on the Transaction Log to come.

References:
http://support.microsoft.com/kb/873235
http://support.microsoft.com/kb/317375/