Transaction Log Corruption and Backup In SQL Server

Introduction

Corruption of SQL Server transaction log files is complicated because its consequences aren’t major except for the fact that they result in the failure of backups. However, that as well is a necessary task to be performed by every server admin for business continuity and thus, must not be ignored.

I/O subsystem is the culprit in most cases of Transaction Log file corruption just like a data file. Till the time this damage has been caused to the active portion of the file – required by SQL Server for some reason – it is possible that the cause remains unknown for a long duration. This may result in a major disturbance because corruption, which is undiscovered, will take equal amount of time in being rectified too, which may make the condition much worse too in the meantime. However, SQL Server has no way of detecting the corruption anyway before any kinds of consequences are surfaced. In order to detect the damage, the server will have to process the complete log file and that too particularly the active parts.

DBCC CHECKDB is a built in utility provided for examining database integrity in SQL Server. Nevertheless, even the utility is unable to detect log file related issues any longer. An active portion of the log will be used in performing the analysis and creating database snapshot for executing the check for consistency.

NOTE: Only the checksums are examined as part of the log record analysis and not the entire transaction log.

This active portion of the transaction log file will be used in case a rollback of transaction takes place when conditions like crash recovery or backup of transaction log is taking place. Besides these two operations, a number of other uses can also take place, which may include mirroring or replication of the database.

Moreover, if the log record is damaged then it is possible for a user to encounter erroneous messages while accessing them or analyzing them with the operation failing at the end. The only point of time where a database is marked or goes into a SUSPECT mode is when the transaction log files is corrupted on a crash recovery or rollback of transaction. In that particular case, your activity of examining log may fail putting the database in an inconsistent state, which is also known as a suspect database.

Backup Strategy

During a backup, if the log file is damaged, there are high chances that its backup process might fail. However, in order to deal with this condition, you can follow the given below work around:

• Firstly, the database can be switched to a Simple Recovery model
• Then, it is required to perform a checkpoint on the database. Doing this will clear up the contents of the active log as long as the server or database no longer require the log to be kept active
• Later, switch back the log database to a full recovery model
• Then finally, re-establish the log file backup by carrying out a differential or full backup

NOTE: This workaround may not work in all possible conditions. This is probably the simplest of all methods for performing the backup essentially skipping the corrupt portion of the transaction log file.

Point to Remember: You have to be aware of the fact that by performing the above-mentioned workaround, you are purposely limiting yourself from the options to perform disaster recovery in case the backup of data is corrupted. You will no longer be able to restore a complete chain of Transaction log backup. The moment you switched to a simple recovery model, the chain got broke and thus the process becomes impossible.

Nevertheless, there is no option in this condition because a roll back is not possible. Thus, backup of the log file cannot take place. Therefore, using the log backup in an essential manner is out of the question.

Bottom Lines

However, in case of any situation where corruption has taken place, ensure that the root cause of the problem is detected to find out what made the log file corrupt to eradicate the issue completely.