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.
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:
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).
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).
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).