Database Transaction Log Full or Unable to write or Recovery Units belonging to database failed to generate a checkpoint

Some time working as a DBA or support engineer you might have faced this issue that DB log file full or recovery unit failed to generate checkpoint. We might get error in SQL error log like below…

Error: 9002, Severity: 17, State: 2.

The transaction log for database 'DB_Name' is full due to 'LOG_BACKUP'.

Could not write a checkpoint record in database DB_Name because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.

Error: 5901, Severity: 16, State: 1.

One or more recovery units belonging to database 'DB_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.

 

Error: 9002, Severity: 17, State: 9.

The transaction log for database '<database name>' is full due to 'LOG_BACKUP'.

 

Error: 9002, Severity: 17, State: 9.

The transaction log for database '<database name>' is full due to 'ACTIVE_TRANSACTION'.

 

Error: 9002, Severity: 17, State: 9.

The transaction log for database '<database name>' is full due to 'AVAILABILITY_REPLICA'.

 

Most of the time RCA behind this issue would be...

·       Lack of additional file space on the DISK

·       The log file is configured not to grow

·       The log file has reached its configured maximum size

If this is the case then you are lucky, adding space or altering DB log file setting can fix the issue. But if this is not the case then...

Solution:

Check the LOG_Reuse_Wait_Desc status by using the below SQL query.

SELECT name, log_reuse_wait_desc  FROM sys.databases where name = N'database_name';         

It will give you a clear glance where the problem is.

Possible value for log_reuse_wait_desc can be…

NOTHING

CHECKPOINT

LOG_BACKUP

ACTIVE_BACKUP_OR_RESTORE

ACTIVE_TRANSACTION

DATABASE_MIRRORING

REPLICATION

DATABASE_SNAPSHOT_CREATION

LOG_SCAN

AVAILABILITY_REPLICA

OLDEST_PAGE

XTP_CHECKPOINT

SLOG_SCAN

In most of the cases, I found, the issue happen because of Log_Backup or DATABASE_MIRRORING or REPLICATION or AVAILABILITY_REPLICA.

So based on the wait description take action and again try to run the SQL to check log_reuse_wait_desc of the DB.