SQL Server error BACKUP detected corruption in the database log


By:   |   Updated: 2015-11-17   |   Comments (3)   |   Related: More > Backup

Problem

Network maintenance was performed in one of our data centers, causing SQL Server to reboot several times. Directly after the maintenance window, SQL Server was checked and no issues were found. The day after the maintenance, we found that the transaction log backups failed due to log file corruption errors in one of the databases. No user complaints were received, but the transaction log continued to grow since we could not perform a successful log backup.

Solution

The history of the transaction log backup job shows the below error, indicating that the log file is corrupted:

BACKUP LOG [MSSQLTipsDB] TO DISK = N'M:\SQLBackup_Dat..." failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally

What is weird is that the full backup completed successfully without any error and when we ran a DBCC CHECKDB no errors were found. Investigating deeper, we found that the transaction log backup will back up all transactions generated since the last log backup, including the corrupted portions, which caused the backup to fail. On the other hand, the full backup only backs up to the beginning of the last active transaction at the time the backup is taken, which is enough for the full backup to be restored and recovered to a consistent point. Typically, the DBCC CHECKDB operation requires the same amount of log as the full backup, at the time the database snapshot is generated.

To solve this corruption problem, you could switch the database to the SIMPLE recovery model and ignore the corrupted portion of the log, then switch the recovery model back to FULL and perform your backups again. This will help in most cases, but in our case it would not work since we are using database mirroring between two datacenters and in order to change the recovery model to SIMPLE we would need to destroy the mirror and then reconfigure which would be a big effort to recreate due to bandwidth between the two datacenters and the size of the database.

SQL Server Database Backup with Continue After Error Option

Another solution is to take a transaction log backup with the CONTINUE_AFTER_ERROR option. Using CONTINUE_AFTER_ERROR, the backup operation continues after encountering a page checksum error.

The backup can be performed using a T-SQL command like the following:

 BACKUP LOG [MSSQLTipsDB] 
 TO DISK = N'M:\SQLBackup_Data\MSSQLTipsDB\ MSSQLTipsDB.trn' 
 WITH NOFORMAT, NOINIT, NAME = N' MSSQLTipsDB - Transaction Log Backup', SKIP, NOREWIND, 
 NOUNLOAD, COMPRESSION, STATS = 10, NO_CHECKSUM, CONTINUE_AFTER_ERROR
 GO

This same option also can be selected using SQL Server Management Studio by checking the option "Continue on error" from the Media Options page on the Back Up Database window as shown below:

sql server backup continue on error

After we used this option to do a manual transaction log backup, the transaction log backup ran successfully and then our scheduled Transaction Log backups continued normally resolving the corruption issue we encountered.

Next Steps


Last Updated: 2015-11-17


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Comments For This Article




Friday, September 20, 2019 - 5:11:07 PM - ChrisN Back To Top (82526)

Worked great thanks! (fyi - Ola's does have this as an input param)


Thursday, June 22, 2017 - 10:32:07 AM - KevinP Back To Top (57932)

Our org has a mirror that I did not feel comfortable pausing but this worked for our group as well to clear the issue and allow the automated backups to proceed with the error cleared.  Thanks

 

 


Monday, August 29, 2016 - 6:35:58 PM - Jaydeep Back To Top (43212)

Solution provided to take log backup when corrupted worked well and here is the query I used to take log backup,

BACKUP LOG DBName

TO DISK='F:\MSSQL\DBName_Tran.bak'

WITH STATS=1, CONTINUE_AFTER_ERROR

GO

Thanks a lot!!

 



download


Recommended Reading

Simple script to backup all SQL Server databases

Script to retrieve SQL Server database backup history and no backups

How to monitor backup and restore progress in SQL Server

Backup to multiple files for faster and smaller SQL Server files

Changing the default SQL Server backup folder





get free sql tips
agree to terms


Learn more about SQL Server tools