Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

The transaction log for database is full due to LOG_BACKUP

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 

Read More on transaction log troubleshooting 

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

 


Author: Tom Collins (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 The transaction log for database is full due to LOG_BACKUP


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