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.
SQL Server Transaction Log files - performance myth
Database autogrow and slow database recovery – t-sql Tuesday 21
Republished with author's permission. See the original post here.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].