What does LSN mean in SQL Server?

Question

What is the meaning of Log Sequence Number? I know that it is of type binary and 10bytes long and it corresponds to the time the transaction happen in DB. But is this a high precision date-time value that is stored in some efficient binary format or is this a function of date-time and something else (for example the serial number of transactions that happen at the same milli second). I did a lot of searching but couldn't find a good answer to this.

Can any one explain with a formula or function that is used to derive the LSN from date-time or anything.

Rida

Answer 1

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

From here.

You should not be concerned with how these are generated.

Bacteriolysis

Mitch, I did check the MSDN before posting here. What I am looking for is how it is made. Like it is important for my particular application. Thanks for your time. – Rida

If it's important for your application, I can only assume you are writing a some sort of low level restore utility? If not, you should not need to know how these are generated. It might change (unlikely, but nothing to stop MS as it is not a published interface). – Bacteriolysis

I am trying to use this for delta extraction windowing in ETL. Not for a restore utility. I want to make sure that it is safe to use LSN for windowing purpose. See my other question [#1137783 – Rida

If I restore a database from a VM snapshot and perform identical transactions on it, would the "new" LSNs generated/incremented be the same as the old one? – Mantua

I'd suggest asking a new question – Bacteriolysis

Answer 2

First of all, sorry for replying on dead post. I came to this thread while searching other operations based on LSN. LSN is nothing but sequential number, as it specifies - Log Sequence Number generated in three hexadecimal parts like 00000016:0000003c:0001 and these parts are as below:

• First part is VLF : Virtual Log Files
• Second/Middle part is OffSet : Offset to the VLF
• Last/Third part is Slot Number inside the block of log/bucket traced based on offset

Generally any DB operation along with start/end of transaction would be stored and each operation of the transaction, obviously with start and commit operation would get LSN in sequential order. Those are not related to any timestamp but can be mapped with timestamp based on sys function. @max has already replied for that.

Any commit operation towards transaction, force to switch to new log block and middle part would be incremented, otherwise last part would be increased based on the size of VLF.

Circumstantiality

@Rida Please refer this link. It provides in depth explanation about LSN. learn.microsoft.com/en-us/sql/relational-databases/… – Circumstantiality

Answer 3

It is an increasing sequence (1,2,3,4,...), not a date time value. From the Microsoft documentation:

The log sequence number (LSN) value is a three-part, uniquely incrementing value. It is used for maintaining the sequence of the transaction log records in the database. This allows SQL Server to maintain the ACID properties and to perform appropriate recovery actions.

Mcmurry

Okay. Then how is this mapped to the date-time? Is it like, when a DML happens, the LSN is incremented and the then date-time value along with the LSN is stored in the mapping table? – Rida

There is no mapping between the LSN and and any date. The LSN is used to put a global ordering on all transactions. As Auassnoi below points out, there is a LNS generated when you do a backup. But that's the only one. – Mcmurry

I read your comment to Mitch Wheat above. I would suggest generating a transaction (insert a date into an application table), then use that as your LSN for before/after/delta purposes. – Mcmurry

@ThomasJones-Low, it appears a mapping from LSN to DateTime is available using sys.fn_cdc_map_lsn_to_time but it requires CDC to be enabled. – Folks

Answer 4

There is no guaranteed way to derive it, but you can guess it from msdb.dbo.backupset on the machine you did the backup on:

SELECT  last_lsn
FROM    msdb.dbo.backupset
WHERE   backup_start_date = @backup_date

This is of course not exact and not reliable.

Gentlefolk

sys.fn_cdc_map_lsn_to_time() to get date from lsn sys.fn_cdc_map_time_to_lsn() to get lsn(s) from date and "relationaloperator" – Hemlock