读取SQL Server事务日志的方法
我想讨论一下SQL Server的事务日志，我四处寻找，找到了一些已经完成的产品，例如Lumigent Log Explorer。但我仍然对此感兴趣。
我知道的方法： 1.直接从物理日志文件读取 2.使用数据库命令/查询、DBCC LOG 或通过 fn_dblog
问题/困难： 1.日志文件结构难以逆向工程。 2.当我进行大量INSERT时，fn_dblog并没有全部，例如，当我INSERT 50000条记录时，fn_dblog只有29616条LOP_INSERT_ROWS记录，这意味着20384条记录被截断？我不知道内部关于fn_dblog的逻辑，有人可以解释一下吗？fn_dblog有局限性吗？
很高兴听到一些关于 SQL Server 事务日志的研究。
I want to discuss about the Transaction Log of SQL Server, and I searched around, found some product which is accomplished, for example, Lumigent Log Explorer. But I am still interested about it.
Methods I known:
1.Read directly from physical log file
2.Using database command/query, DBCC LOG or through fn_dblog
Problems/difficulties:
1.Log file structure is hard to reverse engineered.
2.When I do lots of INSERTs, the fn_dblog didn't has all of them, for example, when I INSERT 50000 records, the fn_dblog just has 29616 LOP_INSERT_ROWS records, which means 20384 records are truncated?I don't know the internal logical about fn_dblog, can someone explain it?Does the fn_dblog has limitations?
Glad to hear some researches about SQL Server Transaction Log.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
打开 LDF 文件的方法有多种，其中大多数就是这样做的 - 打开它。尽管 ApexSQL Log 是一个 SQL Server 事务日志阅读器，它可以读取在线事务日志、分离事务日志和事务日志备份（包括本机和本机压缩），但获取任何人类可读的信息并利用它是很困难的
。作为 LDF 查看器，它专注于操作（DML 和 DDL，总共 45 个），以及执行这些操作所更改的内容：打开 LDF 文件并查看 LDF 文件内容
免责声明：我在 ApexSQL 担任产品支持工程师
There are different ways to open an LDF file, and most of them do just that – opens it. It’s tricky to get any human readable information and make a use of it though
ApexSQL Log is a SQL Server transaction log reader which reads online transaction logs, detached transaction logs and transaction log backups – both native and natively compressed. As an LDF viewer, it’s focused on operations (both DML and DDL, 45 in total), and what was changed by execution of these operations: Open LDF file and view LDF file content
Disclaimer: I work as a Product Support Engineer at ApexSQL
正如 paulsm4 已经指出的那样 - 事务日志并不意味着人类可读，但有多种方法可以使用第三方工具读取它。
唯一真正擅长于此的工具是ApexSQL Log，但您也可以尝试任务蟾蜍
As paulsm4 already pointed out - transaction log is not meant to be human readable but there are ways to read it using third party tools.
Only tool that really specializes in this is ApexSQL Log but you can also try Quest Toad
SQL Server 事务日志并不意味着“人类可读”。它旨在支持 SQL Server，允许事务、读取一致性等。
建议：
如果您确实想了解 SQL Server 内部结构（包括事务日志的工作原理），我强烈建议您阅读本书：
SQL Server 2008 内部原理，Kalen Delaney
这是一本很棒的书；您将学到很多实用且重要的信息。保证满意！
The SQL Server transaction log isn't meant to be "human readable". It's meant to support SQL Server, allowing transactions, read consistency, etc etc.
SUGGESTION:
If you really want to understand SQL Server internals (including how the transaction log works), I strongly encourage you to get a copy of this book:
SQL Server 2008 Internals, Kalen Delaney
It's an excellent book; you will learn a LOT of practical and important information. Satisfaction guaranteed!