Main Reasons for Transaction log chain break

The SQL Server transaction log backup chain or Transaction log chain is the series of sequential transaction log backups related to a database. The log backups are related to each other and are represented through LSN (Log Sequence Number). The transaction log stores the database activity (the transactions). For example, if we insert, delete or update data we will have a transaction registered in the Transaction log. Breaking the transaction log chain will limit the restore point of the backups.

As per Microsoft, below are the main causes for the transaction log breaking:

1) Switching the Recovery Model from FULL to SIMPLE

2) Reverting database with Database Snapshot

3) Log Backups using the Truncate Only option.

4) Log Backup with No_log Option

Lets take an example to understand:-

-> TestDB has a backup schedule of a FULL Backup once a day and transaction log backups every hour.

-> In Evening, a DBA/Developer decides to change the Recovery model from FULL to SIMPLE. This action breaks the log chain. -> Let's say the DBA/Developer changed the Recovery model at 6pm and the data owner at 8pm requests to recover the data to 7pm. Based on the transaction log chain principles the database would only be able to be recovered up to the last transaction log backup before the Recovery Model change at 6pm.

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .