CODEWITHSUNDEEP
sqlsql-serverarchitectureloggingcdc
Faiz
Faiz

Reputation: 5453

What does LSN mean in SQL Server?

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.

Upvotes: 18

Views: 48968

Answers (4)

S.P.
S.P.

Reputation: 151

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.

Upvotes: 9

Quassnoi
Quassnoi

Reputation: 425803

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.

Upvotes: 1

Thomas Jones-Low
Thomas Jones-Low

Reputation: 7161

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.

Upvotes: 6

Mitch Wheat
Mitch Wheat

Reputation: 300769

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.

Upvotes: 22

Related Questions