ansaurus

Question

Answer 1

+8 A:

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.

Mitch Wheat 2009-07-17 13:05:10

+1 for the Jedi mind trick at the end.

Eric 2009-07-17 13:11:05

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.

Faiz 2009-07-19 15:30:45

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).

Mitch Wheat 2009-07-20 00:58:11

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 [http://stackoverflow.com/questions/1137283/in-sql-server-cdc-with-ssis-which-data-should-be-stored-for-windowing-lsn-or-da/1137329#1137329]

Faiz 2009-07-20 06:43:26

Answer 2

+3 A:

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.

Thomas Jones-Low 2009-07-17 13:08:57

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?

Faiz 2009-08-28 11:17:08

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.

Thomas Jones-Low 2009-08-28 17:31:42

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.

Thomas Jones-Low 2009-08-28 17:36:16

Answer 3

+1 A:

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.

Quassnoi 2009-07-17 13:09:30

ansaurus

tags:

views:

answers:

What does LSN mean in SQL Server?

related questions