What does LSN mean in SQL Server?

14 March,2021 by Tom Collins

I was talking to an Auditor recently - who specialises in large Corporate Audits - and they asked me how would I prove a certain database which is backed up is actually restored to another server.  One of the methods I described was using the Log Sequence Numbers (LSN).     

When a database is backed up , the First and Last LSN numbers are recorded, and when a database is restores First and Last LSN mumbers are recorded. 

The Auditor wanted to understand what does the LSN mean? When a record is created in the SQL Server transaction log a new LSN is generated.  Importantly , LSNs are ordered , meaning it is possible to compare one LSN with another i.e was it before or after. 

This sequencing aspect of LSN are used in the database restore sequence. The sequencing of the log chain is critical - and it is up to the DBA to understand what events can break the Log Chain. Read more on The mysterious case of Pseudo SIMPLE RECOVERY model and the transaction log explosive growth   for consequencecs if log chain is unavailable

For example - when the DBA changes the Recovery Model from FULL to SIMPLE - the log chain is broken - and a new FULL backup is required to set up a new chain 

LSNs are exposed through various system tables and functions. 

Read more on Transaction Logs 

Read sql transaction Logs with ::fn_dblog

 

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on What does LSN mean in SQL Server?


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer