Log Reuse Waits Explained: ACTIVE_TRANSACTION

From:

http://sqlity.net/en/1839/log-reuse-waits-explained-active_transaction/

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: ACTIVE_TRANSACTION

Transaction Log

SQL Server uses the transaction log for two purposes. First the transaction log is used for SQL Server to be able to guaranty the durability requirement of the ACID properties. For that it makes sure that at least the portion of the log containing the active transaction has been written to disk successfully before that transaction can be committed.

The second use of the transaction log affects rollbacks. During a transaction that changes a particular page, another transaction might change and commit another change on the same page. Because of that, SQL Server cannot just declare pages that were changed by a transaction as unusable and reload them from disk the next time they are needed. Instead all the changes applied by a transaction need to be undone step by step in reverse order when the transaction is rolled back.

Waiting for a Transaction

For this rollback process SQL Server uses the information captured in the transaction log. Therefore it cannot reuse a virtual log file that contains transaction log records of a transaction that is still active. SQL Server will return a log_reuse_wait_desc value of ACTIVE_TRANSACTION if it runs out of virtual log because of that.

To resolve this wait, you have to commit or rollback all transactions. The safest strategy is to just wait until the transactions finish themselves. Well-designed transactions are usually short lived, but there are many reasons that can turn a normal transaction into a log running one. If you cannot afford to wait for an extra-long running transaction to finish, you might have to kill its session. However, that will cause that transaction to be rolled back. Keep this in mind when designing your application and try to keep all transactions as short as possible.

One common design mistake that can lead to very long running transactions is to require user interaction while the transaction is open. If the person that started the transaction went to lunch while the system is waiting for a response, this transaction can turn into a very-long-running transaction. During this time other transactions, if they are not blocked by this one, will eventually fill up the log and cause the log file to grow.

Summary

SQL Server will return a log_reuse_wait_desc value of ACTIVE_ TRANSACTION if it runs out of virtual log files because of an open transaction. Open transactions prevent virtual log file reuse, because the information in the log records for that transaction might be required to execute a rollback operation.

To prevent this log reuse wait type, make sure you design you transactions to be as short lived as possible and never require end user interaction while a transaction is open.