By: Ahmad Yaseen | Comments (4) | Related: > 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:
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:
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
- Read more about SQL Server Transaction Log Backups.
- Check out the SQL Server Backup tips.
- Check out these tutorials: SQL Server Backup and SQL Server Restore.
About the author
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
