Factors that can delay Log Truncation – LOG BACKUP

12 March,2012 by Jack Vamvas

When log records remain active for a long time – truncation can be delayed. The delays can cause the transaction logs to fill. This can lead to a Error 9002

 In the Full Recovery mode , a Log Backup  will delete the commited transaction log records. In the Simple Recovery , it’s after a CHECKPOINT.

A Production Server Error Log reported this message.

Error: 9002, Severity: 17, State: 2.MessageThe transaction log for database 'MyDB' 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 a : select log_reuse_wait_desc from sys.databases , --returnedLOG BACKUP

 

According to SQL Server BOL this means:

 “A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).

When the log backup is completed, the head of the log is moved forward, and some log space might become reusable”

 

Very straightforward. I completed a Log Backup – and this solved the problem

 How you respond to a Error 9002 depends on the circumstances .  In my case , some very long running transactions caused the log files to grow and fill the disk.  Users were able to read , but any attempt at updates failed.

More on Transaction Log Performance

SQL Server Transaction Log files - performance myth

Database autogrow and slow database recovery – t-sql Tuesday 21


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 Factors that can delay Log Truncation – LOG BACKUP


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