Transaction Log Grows Unexpectedly Due to Orphan Transactions

Problem:

A SQL Server 2005 database’s transaction log file is growing.  The transaction log file is originally 20 gigabytes in size.  Over a three day period, the transaction log grow to 120 gigabytes in size.  Doing the log backup does not release all the space in the transaction log.    The transaction log backup would run successfully every hour, but the backup file would only be a gigabyte in size.  In this case, after each transaction log backup the “Log Space Used (%)” never went below 95%.  Very frustrating 🙁

We found the issue to be orphaned transactions in the database.

First Step: Identify the orphan transactions.

When I ran sys.dm_tran_database_transactions (as seen below), I see that I have transactions that are days old that have not committed.   For example, transaction_id: 874532313, was created on 11/23/2012, and the database_transaction_state is 4 ( transaction has generated log records ).  To state the obvious, we should not have a transaction_id that is days old like this.   That mean this transaction has not been commited, nor has it been rolled back.

Please refer to this link on sys.dm_tran_database_transactions for more information on this dynamic object.

Second Step:

Run “DBCC LOGINFO”.    When we ran this statement, we found that a number of the virtual log files had a status of 2, which mean that it can not be overwritten.

Third Step:

We queried sys.dm_tran_locks, we were able to see that the transaction_id from sys.dm_tran_database_transactions was called request_owner_id in this dynamic object, and that the session was -2. (the picture below  does not correspond to the pictures above, this is only reference).   The -2 for a session owner signifies that this transaction has been orphaned.  The session owner was closed or killed before the transaction could be committed or rolled back.

On side note, we already new that the software writing to this database would produce orphan transactions when it experienced high volume of transactions.

SYS.DM_TRAN_LOCKS - Dynamic Object

Final Step:

With the information in the request_owner_guid column of the sys.dm_tran_locks, you can use the kill command rollback the orphaned transaction.

For example: if you wanted to remove transaction_id/request_owner_id: 1137037072, then run the following command:

—

KILL ‘4C79FC71-D1FF-402A-AB70-95BA150984EB’

GO

—

See the Microsoft link for more information regarding the KILL command and orphaned transactions.

Once the orphaned transactions were killed, we were successfully able to do a transaction log backup, and the “Log Space Used (%)” went from 95% to 0.04%.

In the “DBCC LOGINFO”, the status of all but one line changes from 2 to 0.

Please note that the software did have a another batch process by which we did recover all these transactions.

Final Notes:

Thanks to Dan who found the that the request_owner_guid in sys.dm_tran_locks could be used in the KILL statement.

Also this post will be re-posted, when I can get screen shots of a single transaction_id to be used in all the pictures.