LSN 在 SQL Server 中是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1143119/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What does LSN mean in SQL Server?
提问by Faiz
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.
日志序列号是什么意思？我知道它是二进制类型和 10 字节长，它对应于事务在 DB 中发生的时间。但这是一个以某种有效的二进制格式存储的高精度日期时间值，还是日期时间和其他东西的函数（例如，在同一毫秒内发生的事务序列号）。我做了很多搜索，但找不到一个好的答案。
Can any one explain with a formula or function that is used to derive the LSN from date-time or anything.
任何人都可以用用于从日期时间或任何东西导出 LSN 的公式或函数进行解释。
回答by Mitch Wheat
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.
SQL Server 事务日志中的每条记录都由日志序列号 (LSN) 唯一标识。LSN 是这样排序的，如果 LSN2 大于 LSN1，则 LSN2 引用的日志记录描述的更改发生在日志记录 LSN 描述的更改之后。
From here.
从这里开始。
You should not be concerned with how these are generated.
您不应该关心这些是如何生成的。
回答by Thomas Jones-Low
It is an increasing sequence (1,2,3,4,...), not a date time value. From the Microsoft documentation:
它是一个递增序列 (1,2,3,4,...)，而不是日期时间值。从微软文档：
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.
日志序列号 (LSN) 值是一个由三部分组成的唯一递增值。它用于维护数据库中事务日志记录的顺序。这允许 SQL Server 维护 ACID 属性并执行适当的恢复操作。
回答by Quassnoi
There is no guaranteed way to derive it, but you can guess it from msdb.dbo.backupseton the machine you did the backup on:
没有保证的方法来推导它，但您可以从msdb.dbo.backupset您进行备份的机器上猜测它：
SELECT last_lsn
FROM msdb.dbo.backupset
WHERE backup_start_date = @backup_date
This is of course not exact and not reliable.
这当然不准确也不可靠。
回答by S.P.
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:0001and these parts are as below:
首先，很抱歉回复死帖。我在搜索基于 LSN 的其他操作时来到了这个线程。LSN 只不过是序列号，正如它所指定的 - 以三个十六进制部分生成的日志序列号，如00000016:0000003c:0001，这些部分如下：
- 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
- 第一部分是 VLF：虚拟日志文件
- 第二/中间部分是 OffSet ：偏移到 VLF
- 最后/第三部分是基于偏移量跟踪的日志/存储桶块内的槽号
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.
通常，任何 DB 操作以及事务的开始/结束都将被存储，并且事务的每个操作，显然，开始和提交操作将按顺序获得 LSN。这些与任何时间戳无关，但可以根据 sys 函数与时间戳进行映射。@max 已经对此做出了答复。
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.
任何对事务的提交操作，强制切换到新的日志块，中间部分将增加，否则最后一部分将根据 VLF 的大小增加。
