liveBook · Manning

Log sequence number

As explained in 100 SQL Server Mistakes and How to Avoid Them

An LSN, or log sequence number, is an identifier assigned to a transaction log record to represent its position or identity in the transaction log.

A log sequence number (LSN) identifies a transaction-log record and locates it within the transaction log. SQL Server assigns each record an LSN made from the VLF sequence number, log block ID, and log record ID. section 9.5

Keep these distinctions clear when diagnosing transaction-log behavior:

  • The LSN belongs to an individual log record, while VLFs and log blocks organize the transaction log that contains it. section 9.5
  • Log truncation removes eligible VLF contents rather than the log file itself; active transactions, availability-group replication, or change data capture can keep VLFs active. section 9.5
  • A transaction log cycles through truncated VLFs, but failure to grow when no reusable VLF exists can produce error 9002. section 9.5
Figure 9.4 Transaction log cycle

Use the transaction-log cycle illustration to connect LSN-bearing log records with VLF reuse and log growth. section 9.5

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Defines the three LSN components and relates them to VLFs, log blocks, truncation, cycling, and growth failure.
Examples
Uses DML and DDL log records, full-recovery page logging, and a transaction-log cycle illustration as concrete examples.
What you can do after
Evaluate transaction-log behavior by distinguishing record identity from VLF reuse, truncation, cycling, and growth failure.
How it connects
Connects LSNs with transaction logs, VLFs, log blocks, log records, transactions, availability groups, and change data capture.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.