How to shrink a bloated log file

Introduction

A question commonly seen on Experts Exchange in the SQL Server Zone is a variation on the following theme:

"My Transaction Log is huge!  How do I shrink it?"

This question seems to pop up quite frequently -- I saw it three times in December 2012 and it seemed a good topic for an article.

Background

What is the SQL Server Transaction Log?

According to the documentation, "Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction."

That's a great high level explanation but let's go a little deeper.

In order to maintain the Atomicity part of the ACID test, either all parts of a transaction are committed, or all parts are rolled back.  

In a multi-part transaction (a bank account transaction for example, where one account is credited and one account is debited) we need to make sure that either all updates are comitted or none of them are.

It is the transaction log that keeps track of these changes. In the event of a transactional failure for whatever reason (disk full, disk failure, constraint failure, etc.) we need to know all updates that have been made as a part of the current transaction. When a failure happens, the database engine reads from the transaction log and undoes any uncommitted changes that are a part of the current transaction.

What Causes Growth?

The behavior of the transaction log varies according to the recovery mode that the database is in.

In Full and Bulk Logged Recovery Modes, changes to the database are written to the transaction log in sequence (there are some exceptions for Bulk Logged, but that's outside the scope of this article). The transaction log will grow forever until certain circumstances are met; that enables the database engine to start recording information from the beginning of the file again.

In Simple Recovery Mode, it is only each individual transaction that is recorded in the transaction log before the pages are marked as reusable. Once the transaction is committed and Atomicity is confirmed, the transaction log space is marked for reuse.

It is still possible to generate a large transaction log in Simple Recovery Mode. If you are doing an extremely large update / insert / delete that generates extreme amounts of log information in a single transaction, then the transaction log has to be able to contain the data from that single transaction.

How Do We Shrink It?

What allows shrinking of the transaction log appears to be frequently misunderstood. People often seem to assume that if they perform a full backup of the database that the transaction log space should be able to be reused. This is incorrect.

Returning once again to the documentation, we learn that "Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up."

(It should be noted that the SQL Server documentation contradicts itself here and appears to be partially incorrect in the above entry. In Simple Recovery mode, the transaction log is actually truncated after each checkpoint as per this note.)

Note that the transaction log can't be truncated in full recovery mode until the transaction log is backed up.  

It should also be noted that "truncation" is not the same as "shrinking". Again, we refer to the documentation: "Log truncation is essential because it frees disk space for reuse, but it does not reduce the size if the physical log file. To reduce its physical size, the log file must be shrunk to remove one or more virtual log files that do not hold any part of the logical log".

Transaction log truncation merely means marking the used space in the transaction log file available to be reused. It does not shrink the physical file footprint on the disk.

In order to reclaim the space on disk for an oversize transaction log, you need to perform the following steps:


There are additional concerns when growing a transaction log file like this to make sure that you don't grow it in increments that are too big or too small. I will list a couple of references below for extra reading on this point in particular and this whole article in general.

Conclusion

Transaction Log maintenance is an important aspect of SQL Server Database Administration. It is important for the DBA to understand what causes transaction log growth and how to be able to handle it. This article should help you understand what causes it and how to deal with it.

Further Reading

Kimberly Tripp and Paul Randal of the consultancy sqlskills.com have blogged frequently on this topic and there is some excellent further reading on their blogs: