Managing Long-Running Transactions
One likely cause of the log filling up is a long-running transaction. A long running transaction keeps the transaction log active from the virtual log file containing the first log record of the transaction. Truncation cannot occur from that virtual log file onward.
Important
A very long-running transaction to cause the transaction log to fill. For information about responding to a full transaction log, see Troubleshooting a Full Transaction Log (Error 9002).
Discovering Long-Running Transactions
To look for long-running transactions, use one of the following:
sys.dm_tran_database_transactions
This dynamic management view returns information about transactions at the database level. For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).
For more information, see sys.dm_tran_database_transactions (Transact-SQL).
DBCC OPENTRAN
This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back). For more information, see DBCC OPENTRAN (Transact-SQL).
Note
For information about other factors that can delay log truncation, see Factors That Can Delay Log Truncation.
Stopping a Transaction
You may have to use the KILL statement. Use this statement very carefully, however, especially when critical processes are running. For more information, see KILL (Transact-SQL).