读取 SQL Server 事务日志 [英] Read SQL Server transaction log

问题描述

我们如何读取 SQL Server 事务日志，我知道使用 DBCC 日志(数据库，4)并且它会生成日志输出，现在我想解码十六进制格式的日志记录.

How we can read SQL Server transaction logs, I know using DBCC log (database,4) and it will generate log output now i want to decode Log Record which is is hex format.

0x00003E001C000000A500000001000200BE040000000006021D0000000100000018000000(仅部分数据)

0x00003E001C000000A500000001000200BE040000000006021D0000000100000018000000 (only a part of data)

有没有办法以文本格式读取它或将十六进制数据转换为文本.我想制作一个可以读取日志的工具.第三方工具可用，即 ApexSQL，但它们是付费工具.

is there any method to read it in text format or convert the hex data to text.i want to make a tool that can read logs.third party tools are available i.e ApexSQL but they are paid tools.

推荐答案

您可以使用 sys.fn_dblog 来读取事务日志.示例如下.

You can use sys.fn_dblog to read the transaction log. Example below.

SELECT [RowLog Contents 0],
       [RowLog Contents 1],
       [Current LSN],
       Operation,
       Context,
       [Transaction ID],
       AllocUnitId,
       AllocUnitName,
       [Page ID],
       [Slot ID]
FROM sys.fn_dblog(NULL,NULL)
WHERE Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP', 'LCX_CLUSTERED') 
AND Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS') 

对于 delete 和 insert 操作 IIRC，[RowLog Contents 0] 包含插入和删除的整行.更新有点复杂，因为只能记录部分行.

For a delete and insert operation IIRC the [RowLog Contents 0] contains the whole row inserted and deleted. Updates are a bit more complicated in that only a partial row can be logged.

要解码此行格式，您需要了解行在 SQL Server 中的内部存储方式.Microsoft SQL Server 2008 Internals 详细介绍了这一点.您还可以下载 SQL Server Internals Viewer 以在这方面提供帮助(我相信 SQL Server Internals Viewera href="https://stackoverflow.com/users/12469/mark-s-rasmussen">Mark Rasmussen 的 Orca MDF 也可用，大概有一些代码来解码内部行格式).

To decode this row format you need to understand how rows are stored internally in SQL Server. The book Microsoft SQL Server 2008 Internals covers this in some detail. You can also download the SQL Server Internals Viewer to help in this regard (And I believe the source code for Mark Rasmussen's Orca MDF is available too which presumably has some code to decode the internal row format).

有关在 TSQL 中执行此操作的示例，请参阅 这篇博文 表明只要项目的目标有限，完全有可能从日志中提取有用的信息.不过，编写一个完整的日志读取器来处理对象中的架构更改以及诸如稀疏列(以及下一版本中的列存储索引)之类的内容可能需要大量工作.

For an example of doing this in TSQL see this blog post which demonstrates that it is perfectly possible to extract useful information from the log as long as the aim of the project is limited. Writing a full blown log reader that could cope with schema changes in the objects and things like sparse columns (and column store indexes in next version) would likely be a huge amount of work though.

这篇关于读取 SQL Server 事务日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！