Database Issue: The transaction log for database ‘db_name’ is full due to ‘ACTIVE_BACKUP_OR_RESTORE’.

Today, I was called in the early hours because the transaction log of one of the SQL Server 2016 database was full. I logged onto the SQL server instance via SQL Server Management Studio, and saw the following errors in the SQL Server error log:

The transaction log for database 'OLTPDB' is full
due to 'ACTIVE_BACKUP_OR_RESTORE'.

Reason for failure

This occurs because the full backup for the ‘OLTPDB’ database was still running, and as a result, the transaction log of this database was not freed up for reuse. This caused the transactions that were running against this database to fail.

Issue resolution

As we know, the portion of the transaction log file that is used during the full backup operation must be maintained while the full backup operation is running, which will also be backed up at the end of the full backup operation. That’s because the full database backup contains all the database data at the start of the full backup operation plus the transition log used until the end of the full backup operation. Therefore, to fix the issue, I had to manually increase the size of the transaction log file, to accommodate the high load against the database.

Advertisements

SQL Server Database Backups and Recovery Models – (Part 2)

Checkout the part-2 of my three part article series on SQL Server Database Backups and Recovery Models here, in which you’ll learn about various SQL Server backup types, and how to add and use permanent backup devices and temporary backup devices to perform SQL Server database backups.

In this article, you also learn about differences between various backup options, and how database recovery modes relate to available backup options, and when and how to run a tail-log backup.

This article is published on SSWUG.org.