Share via

Various Causes of Log Chains Breaking Issue in SQL Server

  • Article

SQL Server is a Microsoft’s relational database management system. While working with it, it maintains a backup of the transaction log. There are various transaction logs, which are maintained in a series forming a chain. This formation is called a transaction log backup chain or as log chain. The chain describes an unbroken sequence of transaction log backups, from the starting till the end. The starting log is the place from where the chain started with a full database backup, whereas the end log is the latest log backup performed. 

Any log backup in the log chain is the transaction log, which was generated after the previous log backup. Hence, each log backup is interrelated with each other. As long as the log backups are in sequence, they are not considered broken. Any problem or breakage in the chain could cause a serious problem and would not allow the restore sequence to move past the point where the chain is broken. However, if a differential backup is available, it can be used to bridge the gap.

Usually, the SQL Server Database Engine is responsible for keeping the log chain intact and prevents any kind of gaps in the log backup’s sequence. However, the log chain can be broken anytime by just changing the recovery model to simple and again to full by the administrator.

In case of a simple recovery model where the log chain is broken, you cannot go past the point where the chain is broken. However, you can shift to the full recovery model and take a new differential base and alternatively use it to bridge the gap.

In a worst case scenario, where the backup from the chain is missing or damaged, and there is no differential backup available as well, you can make use of another copy of the backup, if you have made any. In any case, it is always recommended to keep multiple copies of the backups. Such backup copies should be made on a timely basis and also be checked for consistency.

There are various scenarios for the log to be broken. More often, there are various human actions, which can lead to broken log after which you cannot take further log backups until a full differential backup is taken. It can even lead to some data loss.

For example, if you try to take a backup using the Truncate only or no_log options, it can lead to breaking of the chain and thus invalidate all the next log backups. In addition, you might experience breaking of chain due to any of the below mentioned reasons.

  1. Switching the Recovery modes.
  2. Using the Truncate Only option while taking log backups. The Truncate Only option does not work with SQL versions 2008 and above.
  3. Using the No_Log option to take log backup.   The No_Log option will not work with SQL versions 2008 and above.
  4. Using the Database Snapshot for reverting the database. This is not going to be fixed by the differential backup and so requires a full backup of the database.

To fix this breaking of the chain, you need to take a full or differential backup and then continue taking log backups.

Consider a scenario of full recovery model where you would like to restore the broken log chain. In order to restore, the first and the last log sequence numbers are most important and can be obtained from the first_lsn and last_lsn columns of the backupset table in msdb. 

Differential Backups
Differential backups include the log data between the first log sequence number and the last log sequence number. This option recovers to last_lsn, without having the need to use log backups. For the latest backup option, last_lsn is the most recent recovery point possible. However, in case you need an earlier recovery option, you can use any earlier recovery point then last_lsn.

Once the differential backup is done, you can move forward by using the first log backup. However, while inspecting the backup properties, you will see a log backup whose last_lsn would be greater than or equal to the first_lsn from differential backup. You would also see the last_lsn of the differential backup greater than the last_lsn of the differential log backup.

Log Backups in a Log Chain
In the chain log backup, the first log backup, backupset.begins_log_chain is set to 1. A log backup is considered linked to a log chain or consecutive sequence by the first_lsn and last_lsn. A database can be rolled forward from the most recent differential backup by using a sequence of consecutive log backups.

Consider there are two log backups; Backup_A and Backup_B. If the log sequence number of the last log record in Backup_A is greater than or equal to the log sequence number of the first log record in Backup_B, the two log backups are said to be consecutive i.e Backup_A.last_lsn >= Backup_B.first_lsn. 

However, if the above condition does not hold true, then there is a gap between the two backups.

The relationship among the log sequence numbers are mentioned below.
A.last_lsn = B.first_lsn
If A.last_lsn = B.first_lsn, A is usually the log backup taken before B.
A.last_lsn > B.first_lsn

If A.last_lsn > B.first_lsn, then a copy-only log backup was created indicating a case of overlapping. In addition, it could also be possible that the first log backup was taken after a point-in-time recovery.