Blog Post

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.
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 , 


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


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating