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 1)

Whether caused by hardware failure, software errors or malicious actions, there is always a possibility of data loss. You should be prepared to deal with the loss of data and where possible recover your data. How well you plan for the possibility of failure, design of data protection strategy and the implementation of those plans can make the difference between a disaster and an inconvenience. Perhaps the most important tool for ensuring data recovery is a reliable backup of your database.

Checkout the part-1 of my three part article series on SQL SQL Server Database Backups and Recovery Models here, in which you will learn about basics of SQL Server backup process and database recovery models.

This article is published on SSWUG.org.