04 March,2013 by Jack Vamvas
This message appeared in the SQL Server Error Logs , while executing a long running transaction.
The transaction log for database 'database_name' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
I executed the statement below, returning ACTIVE_TRANSACTION :
Select log_reuse_wait_desc from sys.databases where name = ‘db_name’
Active transactions force all logs from the start of the transaction to remain active. Efficient starting and closing of transactions assists log truncation , releasing log space for other transactions.
Some tips on keeping transactions short
1) Return the minimum amount of data
2) In large batch jobs , commit regularly
3) Design transactions to be as short as possible
Factors that can delay Log Truncation – LOG BACKUP
SQL Server - How to find Open Transactions
SQL Server SLEEPING MODE , locks and transactions
MS DTC and sys.dm_tran_active_transactions
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |