Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server error BACKUP detected corruption in the database log


By:   |   Last Updated: 2015-11-17   |   Comments (2)   |   Related Tips: 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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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!!

 


Learn more about SQL Server tools