fn_dblog_如何使用fn_dblog和fn_dump_dblog直接在SQL Server数据库中连续读取事务日志文件数据

fn_dblog

大纲 (Outline)

In this article, we’ll discuss how to read SQL Server transaction logs. This article should provide and insight into how the workload is being captured in the transaction log files. We are going to see how to get the transaction meta-data details and the history of the data will give us a clear idea of how the system is working and helps to decide peak usage hours, also helps to log information about who is doing what?

在本文中，我们将讨论如何读取SQL Server事务日志。 本文应该提供并深入了解如何在事务日志文件中捕获工作负载。 我们将看到如何获取事务元数据详细信息，并且数据的历史记录将使我们对系统的工作方式有一个清晰的了解，并有助于确定高峰使用时间，还有助于记录有关谁在做什么的信息？

In this article you’ll learn how:

在本文中，您将学习如何：

1. To read a SQL transaction log
   读取SQL事务日志
2. to prepare SQL to capture the transaction data
   准备SQL以捕获事务数据
3. to deploy, configure and manage data using T-SQL
   使用T-SQL部署，配置和管理数据
4. to schedule a job using the SQL Server Agent job
   使用SQL Server代理作业安排作业
5. And more ….
   和更多 …。

交易日志的重要性 (Importance of transaction log)

The transaction log is a critical and vital component of the database. Every SQL Server database must have a transaction log and it records all transactions made on the database. If there is a system failure, accidental deletes, audits, point-in-time restores, recovery, or setting up High-availability, you will need a transaction-log to bring your database online.

事务日志是数据库的重要组成部分。 每个SQL Server数据库都必须有一个事务日志，并且它记录在数据库上进行的所有事务。 如果发生系统故障，意外删除，审计，时间点还原，恢复或设置高可用性，则需要事务日志以使数据库联机。

读取SQL Server事务日志 (Read SQL Server transaction log)

In this article, we’ll discuss how to read transaction log to measure the number the Inserts, Updates, and Deletes operations. Also, discuss how easy is to find those affected objects against these transactions. Surprisingly, it’s not that complex to measure the entire process but obviously, you do need a database with FULL recovery model or SIMPLE if you get in there fast enough before the log clears. So what do you need to do? Well, first things first we need to look at the log.

在本文中，我们将讨论如何读取事务日志以测量“插入”，“更新”和“删除”操作的数量。 另外，讨论针对这些事务查找那些受影响的对象有多么容易。 令人惊讶的是，衡量整个过程并不那么复杂，但是显然，如果您在清除日志之前足够快地进入数据库，则确实需要具有FULL恢复模型或SIMPLE的数据库。 那你需要做什么呢？ 好吧，首先，我们需要查看日志。

In general, the transaction logs cannot be read directly because the data is in binary and it’s not in human-readable format. Indeed, the logged transactions details can be read using undocumented functions such as fn_dblog () and fn_dump_dblog (). This gives us an option to read each logged data in the transaction files. It can also be used in conjunction with preparing recovery solutions. Let us get statred to see how to use the fn_dbLog function, make sure to select columns that you’re interested in. fn_dblog is an organized transaction data-set to view the details of the transaction log. All of the transactions are carried forward in the same order in which it got created and each transaction is associated with specific LSN (Log Sequence Number).

通常，不能直接读取事务日志，因为数据是二进制数据，并且不是人类可读的格式。 实际上，可以使用未记录的函数（例如fn_dblog