28 December,2018 by Tom Collins
Question: I'm getting the The transaction log for database is full due to LOG BACKUP Error 9002 Severity 17 State 2 error message .
When I look at the Message associated with the error message the details are
"One or more recovery units belonging to database 'database_name' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure"
I can see clearly the disk space available on the drive is insufficient - which presumably is restricting the log file expansion.
What options are there to fix this issue with minimum disruption to services?
Answer: The simple answer is to look at these options and identify which is the most effective method based on YOUR particular details. Quite often these situation can be in a critical production environment . It is important you take all necessary precautions including : a) a proper recovery point b) proper review of underlying causes and understanding the root cause . Apply the FORDEC decision making process
Option 1 - Backing up the log.
Option 2 - Release disk space to allow transaction log file to automatically grow.
Option 3 - Move the transaction log file to a disk drive with enough useful space.
Option 4 - Add a transaction log file on a different disk with enough useful space
Option 5 - Complete \ kill a long-running transaction
The transaction log for database is full due to ACTIVE_TRANSACTION
Factors that can delay Log Truncation – LOG BACKUP (SQL Server ...
SQL Server Transaction Log files - performance myth
SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |