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.
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).
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).
See Also