Understanding Log Sequence Numbers for SQL Server Transaction Log Backups and Full Backups
Understanding Log Sequence Numbers for SQL Server Transaction Log Backups and Full Backups
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolUnderstanding Log Sequence Numbers for SQL Server Transaction Log Backups and Full Backups
July 22, 2019 by Rajendra Gupta 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. 1234567 BACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDB.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUMGOdeclare @backupSetId as intselect @backupSetId = position from msdb..backupset where database_name=N'SampleDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'SampleDB' )if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''SampleDB'' not found.', 16, 1) endRESTORE VERIFYONLY FROM DISK = N'E:\DBbackup\SampleDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWINDGO 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. 1234567891011121314151617181920 SELECT s.database_name,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,CAST(s.database_backup_lsn AS VARCHAR(50)) AS database_backup_lsn,CAST(s.checkpoint_lsn AS VARCHAR(50)) AS checkpoint_lsn,CASE s.[type] WHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Transaction Log'END AS BackupType,s.recovery_modelFROM msdb.dbo.backupset sINNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_idWHERE s.database_name = DB_NAME() ORDER BY backup_start_date DESC, backup_finish_dateGO 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 valueExample 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. 1234567891011121314151617181920212223242526272829303132333435 BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB1.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB2.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB3.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB4.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB5.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB6.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB7.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB8.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GO Query Window 2: Execute the following query to generate transaction log activity every 30 seconds. 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 INSERT INTO ExamResultVALUES('Lily', 'Maths', 65);Go 100WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResultVALUES('Lily', 'Maths', 65);Go 100WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResultVALUES('Lily', 'Maths', 65);Go 100WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResultVALUES('Lily', 'Maths', 65);Go 100WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResultVALUES('Lily', 'Maths', 65);Go 100WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResultVALUES('Lily', 'Maths', 65);Go 100 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 backupExample 3 Multiple full backups and subsequent log backup
Let’s take two full backup and subsequent SQL Server transaction log backups. 123456789101112131415 BACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDB1.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUMGOBACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDB2.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUMGO WAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB9.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOWAITFOR DELAY '00:01:00.000'BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB10.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GO 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 backupExample 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. 12 BACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDBfull23.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM In the second query window, execute the query to take transaction log backup. 123 BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB23.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GO 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 backupAnswers 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 sequenceConclusion
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. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022