通过读取SQL Server事务日志进行审核

In the previous articles of this series, we discussed the reasons behind auditing the different SQL Server instance and database events, how to audit the SQL Server instance manually and using the Extended Events and Triggers methods. In this article, we will show how to audit your SQL Server instance by reading the content of the SQL Transaction Log file of the user database.

在本系列的前几篇文章中，我们讨论了审核不同SQL Server实例和数据库事件的原因，如何手动审核SQL Server实例以及使用扩展事件和触发器方法。 在本文中，我们将展示如何通过读取用户数据库SQL事务日志文件的内容来审核SQL Server实例。

交易记录概述 (Transaction Log Overview)

In SQL Server, the database consists of two types of files, the database data file, in which the tables data is stored, and the SQL Server Transaction Log file that is used to log the different types of data modification operations. For more information about the SQL Server Transaction Log file check the SQL Server Transaction Log Architecture article.

在SQL Server中，数据库由两种类型的文件组成：存储表数据的数据库数据文件和用于记录不同类型的数据修改操作SQL Server事务日志文件。 有关SQL Server事务日志文件的更多信息，请参见SQL Server事务日志体系结构文章。

The events that are logged to the SQL Server Transaction Log files include the data modification operations, such as INSERT, UPDATE and DELETE statements, database tables and indexes DDL operations, such as CREATE and DROP statements, database tables truncation operations, and page allocation and deallocation operations.

记录到SQL Server事务日志文件的事件包括数据修改操作（例如INSERT，UPDATE和DELETE语句），数据库表和索引DDL操作（例如CREATE和DROP语句），数据库表截断操作以及页分配和释放操作。

为什么是事务日志文件 (Why the Transaction Log File)

For many SQL Server database administrators, including me at the beginning of my working days, the SQL Server Transaction Log file was a black box that is opened only by the SQL Server Engine, with no way to dive inside it and check its content.

对于包括我在内的许多SQL Server数据库管理员来说，SQL Server事务日志文件是一个黑匣子，仅由SQL Server Engine打开，无法深入其中并检查其内容。

SQL Server Engine writes valuable information in the SQL Server Transaction Log file, that describes the different types of events performed on the SQL Server and database instance levels. This information will be logged to the SQL Transaction Log file with no additional configuration required from the database administrator side. In other words, that events information is there in the SQL transaction log file and waiting for a reader to take benefits from it.

SQL Server Engine在SQL Server事务日志文件中写入有价值的信息，该信息描述了在SQL Server和数据库实例级别执行的不同类型的事件。 该信息将被记录到SQL事务日志文件中，而数据库管理员无需进行其他配置。 换句话说，事件信息位于SQL事务日志文件中，并在等待读者从中受益。

读取事务日志文件 (Reading the Transaction Log File)

SQL Server provides us with number of built-in functions that can be used to read the online SQL Server Transaction Log file data. In order to understand these methods, we will try to perform multiple data modification operations on a database table and use these methods to read the SQL Transaction Log file:

SQL Server为我们提供了许多内置函数，可用于读取联机SQL Server事务日志文件数据。 为了理解这些方法，我们将尝试对数据库表执行多种数据修改操作，并使用以下方法读取SQL事务日志文件：