What is a lsn:log Sequence number

January 17th, 2012

LSNs, or Log Sequence Numbers, are explained on MSDN in Introduction to Log Sequence Numbers:

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

There are several places where LSNs are. For Examplesys.database_recovery_status has the columns Last_log_backup_lsn andfork_point_lsn, sys.database_mirroring Has the MIRRORING_FAILOVER_LSN column and the msdb table backupset contains First_lsn, LAST_LSN, Checkpoint_lsn,database_b ACKUP_LSN, Fork_point_lsn and DIFFERENTIAL_BASE_LSN. Not surprisingly all this places where LSNs are exposed are related to backup and recovery (mirroring is a form of recove RY). The LSN is exposed a numeric (25,0) value so can be compared:a bigger LSN number value means a later log Sequen Ce number and therefore it can indicate if more log needs to is backed up or recovered.

Yet we can dig deeper. Look in the LSN as exposed by the Fn_dblog function:

SELECT * from Fn_dblog (null, NULL);

current LSN	operation	context	transaction ID	...
00000014:00000061:0001	Lop_begin_xact	Lcx_null	0000:000001e4
00000014:00000061:0002	Lop_insert_rows	Lcx_null	0000:000001e4
...
00000014:00000061:02ea	Lop_insert_rows	Lcx_null	0000:000001e4
00000014:000000d9:0001	Lop_insert_rows	Lcx_null	0000:000001e4
00000014:000000d9:0002	Lop_insert_rows	Lcx_null	0000:000001e4
...
00000014:00000153:021e	Lop_insert_rows	Lcx_null	0000:000001e4
00000014:00000153:021f	Lop_commit_xact	Lcx_null	0000:000001e4
00000014:000001ab:0001	Lop_begin_xact	Lcx_null	0000:000001ea
...
00000014:000001ab:01ac	Lop_insert_rows	Lcx_null	0000:000001ea
00000015:00000010:0001	Lop_insert_rows	Lcx_null	0000:000001ea

The LSN is shown as a three part structure. The "I" seems to stay the same (the), the middle part apparently has the some erratic increases and the last part SE EMS to increase monotonically but it resets back to 0 when the middle part changes. It is a very easier to understand what ' s happening the know what the three parts E number. The middle part are the offset to the "log Block", the last part is the slot number inside the log block Virtual log file S

SQL Server manages the log by splitting to regions called VLFs, Virtual log Files, as explained in the Transaction log P Hysical architecture. We can inspect the VLFs of a database using DBCC loginfo:

 Fileid FileSize Startoffset fseqno Status Parity createlsn-------------------------------------------------      -----------------------2 253952 8192 20 2 64 0 2 253952 262144 21      2 64 0 2 270336 516096 22 2 64 20000000021700747 2-262144 786432 23 2 64 21000000013600747 2 262144 1048576 24 2 64-22000
000024900748..           2 393216 16973824 75 2 64 74000000013600748 2 393216 17367040 0-0       0 74000000013600748 2 393216 17760256 0 0 0 74000000013600748 2 524288 18153472 0 0 0 74000000013600748 (row (s) affected) 

So back to our Fn_dblog result:the current LSN value of 00000014:00000061:0001 means the following log record:the VLF SE Quence number 0x14 (decimal) in the "Log block starting" at offset 0x61 in the VLF (measured in units of bytes) the S Lot Number 1

From the DBCC loginfo output we know this VLF with sequence number 0x14 are starting at offset 8192 in the LDF file and Has a of size of 253952. It is the the "the" VLF in the log file. The next LSNs increase the slot number until the LSN 00000014:00000061:02ea and then the LSN changes to 00000014:000000D9: 0001. This means that "block," starts at offset 0x61 has filled and the next blocks, from offset 0xd9, started to be used. If We do the math, 0xd9-0x61 are 0x78 (or), which is exactly 60Kb. You may remember this maximum SQL Server log block size is 60Kb.

Our transaction continued to insert records until the LSN 00000014:00000153:021e then at the next LSN we have a commit Operation (Lop_commit_xact). The next LSN, which is for the next transaction start, are at LSN 00000014:000001ab:0001. What does this tell us? The transaction terminated with the INSERT operation at LSN 00000014:00000153:021e and then it issued a COMMIT. The commit operation generated one more log record, the one in LSN 00000014:00000153:021f and then asked for the commit LS N to be hardened. This causes the "log block containing" This LSN (the log block starting at offset 0x153 in the VFL 0x14) to be closed and WR Itten to disk (along and any other not-yet-written log-block that isahead of this block). The next block can start immediately to the LSN 00000014:000001ab:0001, so the next operation would have. If We do the math we can-so-0x1ab-0x153-0x58 (decimal) so-last log block had 44Kb.

I hope by ' now ' clear what happened next in ' Fn_dblogoutput I shown:the Transaction continues to insert records Gener ating more LSNs. The entry at 00000014:000001AB:01AC isn't only the "last one", the but is also the last one in the cur Rent VLF. The next LSN is 00000015:00000010:0001, the LSN at slot 1 in the "the" the "the", "0x10" of the VLF with sequence Nu Mber 0x15. Decimal LSNs

What about those LSN numbers like the 20000000021700747 createlsn value shown in DBCC loginfo output? They are still three part LSNs, but the "values are represented in decimal." 20000000021700747 would be the LSN 00000014:000000d9:02eb. Log Flush Waits

Look at the following Fn_dblog output:

... 00000016:0000003c:0001 lop_begin_xact lcx_null 0000:0000057f 00000016:0000003c:0002 LOP_INSERT_ROWS P 0000:0000057f 00000016:0000003c:0003 lop_commit_xact lcx_null 0000:0000057f 00000016:0000003d:0001 LOP_BEG In_xact lcx_null 0000:00000580 00000016:0000003d:0002 lop_insert_rows lcx_heap 0000:00000580 00000016:00 00003d:0003 lop_commit_xact lcx_null 0000:00000580 00000016:0000003e:0001 lop_begin_xact LCX_NULL 0000:0     0000581 00000016:0000003e:0002 lop_insert_rows lcx_heap 0000:00000581 00000016:0000003e:0003   Lcx_null 0000:00000581 00000016:0000003f:0001 lop_begin_xact lcx_null 0000:00000582 Lop_insert_rows lcx_heap 0000:00000582 00000016:0000003f:0003 lop_commit_xact lcx_null 0000:00000582.
. 

Can you spot the problem? Notice how the LSN slot number stays in 1,2,3 and the log block number grows very frequently:3c, 3d, 3e, 3f ... This is the log signature of a sequence to single row inserts, each committed individually. After each INSERT the "application had to" for the "log block to" is flushed to disk. The logs blocks were all of minimal size, bytes.

Here are how I generated this log:

SET NOCOUNT on;