Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Factors that can delay Log Truncation – LOG BACKUP

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.
Message
The 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 , 
--returned
LOG BACKUP

 

According to 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.

See Also

SQL Server Transaction Log files - performance myth

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

Comments

Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...