SQL Server Backup Detected Corruption In the Database Log
Overview
MS SQL Server is tricky business as far as the significance of database is concerned which is obvious in case of an RDBMS because of its extensive usage in enterprises. Therefore, conditions of database or its log file corruption could result in major losses. This could probably affect the business and as a result, the monetary benefits too could be put at risk. When SQL Server backup detected corruption in the database log and you have no idea what to do, the following blog shall help. The blog shares solution on how to deal with backup detected corruption in the transaction log.
The Condition of Detection
During maintenance, the Server has to be rebooted more than a couple of times, and in a similar condition, it is possible to encounter a situation like this. The transaction log backup might fail and this is because of errors showing up in the databases regarding log file corruption. This doesn’t particularly cause a major issue to arise instantly, but it does make the transaction log to continue growing as the backup could not be performed.
Solved: SQL Server Backup Detected Corruption in the Database log
Transaction log backup job error that indicates corruption in database log file:
The problem here is that neither during a full backup nor after running DBCC CHECKDB did the error appear but the deeper investigation led to something else. In this case, it was discovered that all transactions logged since the last backup will be backed up by transaction log backup process, which will include the corrupted portions as well that had previously caused the backup process to fail.
Meanwhile, the full backup only generates the backup of the beginning portions of last active transactions during backup, which is appropriate for a full backup to be recovered / restored into a consistent state.
Generally, it is necessary for DBCC CHECKDB to have the exact amount of log transactions as in the full backup while generating database snapshot. In order to resolve the condition by fixing the corruption issue, one can switch the database to a SIMPLE recovery model and completely ignore the corrupted parts of the log. Later, a switch from recovery model to FULL backup can be made to perform the backups again.
In most cases this would work, however not in the one discussed above.
NOTE: If yours is a different case of SQL Server backup detecting corruption in the database log then the resolution can be implemented.
What You Must Do
If MS SQL Server backup detected corruption in the database log and you want to resolve the condition, follow the suggestion laid out below:
- Set the database to SIMPLE recovery model.
- Now perform a manual checkpoint for moving the point-in-time of the database log file.
- Then set the database back to FULL recovery model.
- Later, carry out a FULL backup process. This will be necessary for completing the Database LOG file backup otherwise an error will be received.
- At the end, performing a backup for the transaction log file of the database is necessary. This stage must complete successfully if everything goes right.
Why Does The Situation Take Place?
The nature of transaction log backup by default is to backup the complete transaction log entries that have been generated since the last backup that took place. Therefore, when the backup generation takes place, it will attempt the backup of even the corrupt portion that happens to be a part of the transaction log, resulting in a failure.
Nevertheless, as part of a preventive measure the integrity of transaction logs along with the Server databases can be checked on an alternate day basis in order to ensure that its state is stable.
Conclusion:
MS SQL Server backup detected corruption in the database log as part of the procedure. Therefore, dealing with the results in the defined manner will be suitable for resolving the condition to an extent. However, there are consequences both; negative as well as positive depending on your situation.