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

The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

Note

LSNs are values of data type numeric(25,0). Arithmetic operations (for example, addition or subtraction) are not meaningful and must not be used with LSNs.

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

Viewing LSNs Used by Backup and Restore

The LSN of a log record at which a given backup and restore event occurred is viewable using one or more of the following:

• backupset

• backupfile

• sys.database_files; sys.master_files

• RESTORE HEADERONLY

• RESTORE FILELISTONLY

Note

LSNs also appear in some message texts.

See Also

Concepts

Determining the Time Interval Contained in a Log Backup

Log Sequence Numbers and Restore Planning

Recovery Paths

Introduction to Transaction Logs