Rajendra Gupta
LSN of full backup and log backup

Understanding Log Sequence Numbers for SQL Server Transaction Log Backups and Full Backups

July 22, 2019 by

This article explores the SQL Server Transaction log backups and log sequence number (LSN) in combination with the Full backups.

SQL Server Backup Introduction

The database backups are crucial for database recovery and disaster planning. It is the primary duty of a DBA to define the backup policy for each database based on the criticality, Recovery time object (RTO) and Recovery Point Objective (RPO). The database backups are useful even if you implemented the disaster recovery solutions like HADR SQL Server Always On. To meet these requirements, we schedule native or third-party backup tools to take database backups. We have the following database backups in SQL Server.

  • Full backup: It is a complete database backup and allows to restore database till the time backup was completed. It is the most straightforward form of database backup
  • Differential backup: It contains changes from the last full backup. These are cumulative backups
  • Log backup: It takes SQL Server transaction log backup and contains data from the last log backup or first full database backup

We are not going to talk in detail about these backup types. You can refer to the article Understanding SQL Server Backup Types to gather details about them.

DBA combines these database backups to have a backup policy of a database. Usually, for large databases, we take a weekly full backup and the combination of differential and log backups in between. These database backups build a log chain, and it is very critical to maintain the log chain for database backups. We should also be aware of the actions that can break the log sequence. If the LSN chain is broken, it is difficult to restore the database, and in case of any disaster, if we cannot restore the database, it might create a problematic scenario for the DBA.

Suppose we have the following backup policy for a critical database.

  • Weekly Full backup
  • Daily differential backup
  • Hourly SQL Server transaction log backup

In the above scenario, let’s say someone took a Full database backup after the SQL Server Transaction Log backup.

Questions

Now let me ask a few questions here:

  • Will the full backup break the LSN chain?
  • The transaction log backup after the full backup contains data from the full backup or not?
  • If the database size is huge and full backup takes 4-5 hours to complete, what happens to hourly log backup?
  • Would log backup work while the full backup is in progress?

If you know the answers to these questions, you can skip this article. I am sure most of the DBA would be confused and fail to answer these questions.

Overview of SQL Server backups and LSN

Let’s prepare the environment to explore answers to these questions.

Example 1: Full Database backup and LSN

Create a sample database and take a full database backup using the following query. This query takes the full backup, performs CHECKSUM and verify backup once finished.

Once backup is finished, execute the following query in database context for which we want to extract the details. It checks backup history for full, differential and SQL Server transaction log backups. It also gives the log sequence details for each backup types.

In the output, we can look at the following values.

  • First_lsn: It shows the log sequence number of the oldest log record
  • Last_LSN: it shows the Last log sequence number in the backup set
  • Checkpoint_LSN: it is the log sequence number of the last checkpoint
  • Database_LSN: it shows the LSN of the last full database backup. In this case, we are taking a first full backup. Therefore, it shows the zero value

SQL Server Transaction Log backup and  LSN values

Example 2: Transaction log backup and LSN

Now open two new query windows.

Query Window 1: Execute the following query to take SQL Server Transaction Log backup at every 1-minute interval.

Query Window 2: Execute the following query to generate transaction log activity every 30 seconds.

Once both the query gets completed, rerun the query to check the log backup history.

In this screenshot, you can note the following things.

  • Database_backup_LSN for all log backup points to last full backup first_lsn
  • For the first log backup, Last_lsn value corresponds to last_lsn of the full backup
  • For all subsequent log backup, first_lsn is the last_lsn value of previous SQL Server Transaction Log backup

corelation of various log backups and LSN

Example 3: Multiple full backups and subsequent log backup

Let’s take two full backup and subsequent SQL Server transaction log backups.

Let’s view the database backup LSN information. In the following screenshot, we can note the following things.

  • For the first full backup after the log backups, checkpoint_lsn and first_lsn values are same, and database_backup_lsn still point to first_lsn of the initial full backup
  • For the next full backup also, checkpoint_lsn and first_lsn values are the same but the database_backup_lsn value changes to first_lsn of the previous full backup
  • Log backup after the full backup has the first_LSN value equals to last_lsn of the last log backup. It shows that log backup does not break the LSN chain and it continues to maintain the chain since the last log backup

LSN of full backup and log backup

Example 4: Take SQL Server transaction log backup while a full backup is in running state

In the next step, let’s start log backup while the full backup is in running state. Open two new query window in SSMS.

Execute the following query to take full database backup in the first window.

In the second query window, execute the query to take transaction log backup.

Once both the backups are finished, view the database backup history again.

Observe the following things from the query output.

  • Full database backup started at 2019-07-16 13:26:20.000 and log backup started at 2019-07-16 13:26:21.000
  • Log backup started before full database backup finished at 2019-07-16 13:26:28.000
  • Both full and log backup show the similar database_backup_lsn because transaction log backup started before completion of the full backup, it references to old full backup
  • The log backup LSN still matches with the last_LSN of previous transaction log backup. It shows that SQL Server transaction log backup maintains the log chain even if it is running while the full backup is in progress. Log truncation cannot occur during the full backup even if you are running the transaction log backup. It will occur with the first log backup after the full backup

LSN of full backup and SQL Server log backup running at the same time

Answers to initially asked questions

Let’s go back to questions asked initially and find out the answers.

  • Will the full backup break the LSN chain?

    No, Full backup does not break the log sequence chain.

  • The transaction log backup after the full backup contains data from the full backup or not?

    The transaction log backup takes data from the last LSN of previous log backup. It maintains the log chain; however, we can restore the full backup followed by the transaction log backup. SQL Server prepares a restoration plan as per the LSN during restore planning.

  • If the database size is enormous and full backup takes 4-5 hours to complete, what happens to hourly log backup?

    Nothing, Log backup can continue to run as usual. The only difference is that it cannot truncate the transaction log due to in-progress full backup. Once we execute log backup after full backup completion, it truncates the log as well.

  • Would SQL Server transaction log backup work while the full backup is in progress?

    As per the previous question, transaction log backup execution can work as usual. You will not face any failure in the transaction log backup job due to full backup progress.

Log sequence mismatch common reason

We can have many reasons that can break the log chain for database backup. If a particular database backup is corrupted or missing, it might impact the log chain and would make it difficult to restore the database.

Let’s explore the reasons that can break the log sequence.

  • Sometimes DBA changes the recovery model to simple to execute bulk transactions that can lead to higher log growth. If we switch the recovery model from Full to Simple, it breaks the log sequence. Switching back the recovery model to FULL requires to set up a log chain again using the full backup and the subsequent SQL Server transaction log backups
  • Switching recovery model from bulk-logged to simple also breaks the log sequence
  • Suppose we take differential backup daily. The base for the differential backup is the last full backup. We should not take on-demand full backup in this case because it breaks the chain for the differential backup. Take full backup with the Copy_Only option in this case
  • If we revert to a database snapshot, it also breaks the log sequence

Conclusion

In this article, we explored the concept of a SQL Server transaction log backup with LSN and how SQL Server maintains the chain with multiple scenarios. DBA should be aware of these scenarios to avoid any mistake that can break the log sequence. You should also perform the database restoration drills on a timely basis to test the recovery of databases from the backups.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
1,638 Views