Log cannot be reused, see the log_reuse_wait_desc column in sys.databases – SQL Server Error 9002

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

 

Read More

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






Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on Log cannot be reused, see the log_reuse_wait_desc column in sys.databases – SQL Server Error 9002


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer