读取SQL Server事务日志

介绍 (Introduction)

There has always been some debate as to whether or not there are real benefits to be gained from accessing the information in the transaction log. This article will endeavor to answer that question by looking at the following:

关于访问事务日志中的信息是否会带来真正的好处，一直存在争议。 本文将通过查看以下内容来努力回答该问题：

• What is the SQL Server Transaction Log?
  什么是SQL Server事务日志？
• What information is stored in the transaction log?
  事务日志中存储什么信息？
• What can be gained by accessing the information in the transaction log?
  通过访问事务日志中的信息可以获得什么？
• How does the transaction log work?
  事务日志如何工作？
• What tools are available for reading the transaction log?
  哪些工具可用于读取事务日志？
• And ultimately, is this something we should be doing at all?
  最终，这是我们应该做的吗？

什么是SQL Server事务日志？ (What is the SQL Server Transaction Log?)

The main purpose of the SQL Server Transaction Log is to ensure that your database can be brought back to a consistent state in case of a system failure. In addition, it is used to perform other functions such as rollbacks when a rollback command is issued and supporting transactional replication and high availability solutions.

SQL Server事务日志的主要目的是确保在系统出现故障时可以将数据库恢复到一致状态。 此外，它还用于执行其他功能，例如在发出回滚命令时回滚，并支持事务复制和高可用性解决方案。

SQL Server logs information about each and every transaction made, into the transaction log before the changes are written to the database. The amount of information logged depends on the recovery model of your database. SQL Server offers 3 different recovery models: Full, Bulk Logged and Simple.

在将更改写入数据库之前，SQL Server将有关每个事务的信息记录到事务日志中。 记录的信息量取决于数据库的恢复模型。 SQL Server提供了3种不同的恢复模型：完整，批量记录和简单。

恢复模型 (Recovery Models)

FULL recovery model 完整恢复模式

This recovery model logs every change to every row as well as a copy of each page added to indexes or table. As such the log contains enough information to be able to completely re- construct every action which occurred on the database, allowing you to restore your database back to any specific time, provided that you have a full log chain. All entries are kept in the online transaction log until the log is backed up, after which only active transactions will remain in the online log. This means that in order to get information about completed transactions from the log, the log backups will have to be taken into account.

此恢复模型记录对每一行的所有更改以及添加到索引或表的每个页面的副本。 这样，日志包含足够的信息，能够完全重建数据库上发生的每个操作，只要您拥有完整的日志链，就可以将数据库还原到任何特定时间。 所有条目都保留在联机事务日志中，直到备份日志为止，之后，仅活动事务将保留在联机日志中。 这意味着，为了从日志中获取有关已完成事务的信息，必须考虑日志备份。

BULK_LOGGED recovery model BULK_LOGGED恢复模型

When you are using the BULK_LOGGED recovery option, all minimally logged operations are not written to the Log. Minimally logged operations are operations such as SELECT INTO, BULK INSERT and Index operations. Essentially just enough information is logged to be able to undo the transaction, but not enough to redo it. The log is handled in much the same way as the FULL recovery model, and inactive transactions are moved to the log backup when a log backup is taken. Of course no information about bulk transactions are available.

当您使用BULK_LOGGED恢复选项时，所有最少记录的操作都不会写入日志。 最少记录的操作是诸如SELECT INTO，BULK INSERT和Index操作之类的操作。 本质上，仅记录了足以撤消事务的信息，但还不足以重做它。 日志的处理方式与FULL恢复模型完全相同，在进行日志备份时，非活动事务将移至日志备份中。 当然，没有有关批量交易的信息。

SIMPLE recovery model 简单的恢复模型

The SIMPLE recovery model only logs enough information to allow you to recover your database. All inactive log entries are automatically truncated when a checkpoint occurs. All operations are still being logged, but as soon as a checkpoint occurs the log is automatically truncated, which means that it becomes available for re-use and older log entries can now be over-written.

SIMPLE恢复模型仅记录足够的信息，以允许您恢复数据库。 发生检查点时，所有不活动的日志条目都会被自动截断。 所有操作仍在进行日志记录，但是一旦出现检查点，日志就会自动被截断，这意味着该日志可供重新使用，并且较旧的日志条目现在可以被覆盖。

事务日志中记录了什么？ (What is logged in the transaction log?)

SQL Server logs every event in a database to a more or lesser extent.

SQL Server或多或少地将每个事件记录在数据库中。

• When a transaction begins or ends
  交易开始或结束时
• Every update , insert or delete
  每次更新，插入或删除
• Drop and creation of tables and indexes
  删除和创建表和索引
• Extent and page allocations and de-allocations
  范围和页面分配与取消分配
• Truncation of tables
  截断表
• All locks
  所有锁

Some operations may be minimally logged when the database is in simple or bulk logged recovery model, such as bcp, BULK INSERT, SELECT INTO and SELECT … INSERT command.

当数据库处于简单或批量记录的恢复模型时，某些操作的记录可能最少，例如bcp，BULK INSERT，SELECT INTO和SELECT…INSERT命令。

阅读事务日志可以得到什么？ (What can be gained from reading the Transaction Log?)

There are four main reasons why one might be interested in reading the transaction log.

为什么可能有兴趣阅读事务日志的主要原因有四个。

审计\取证 (Auditing \ Forensics)

SQL Server offers a myriad of methods which can be implemented as preventative measures to circumvent the need to use the SQL Server transaction log to audit a database. This includes SQL Server Auditing (SQL 2008 +), traces and extended events, change data capture to name but a few. Most of these with the exception of the default trace, requires implementation prior to any event occurring.

SQL Server提供了许多方法，这些方法可以作为预防措施来实现，从而避免了使用SQL Server事务日志来审核数据库的需求。 这包括SQL Server审核（SQL 2008 +），跟踪和扩展事件，将数据捕获更改为仅举几例。 除默认跟踪外，其中大多数都需要在任何事件发生之前实施。

The SQL Server transaction log however, is always present and as such can offer valuable information after an event occurred despite the fact that no special advanced configuration has been done.

但是，SQL Server