本文共 12580 字,大约阅读时间需要 41 分钟。
fn_dblog
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:
在本文中,您将学习如何:
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数据库都必须有一个事务日志,并且它记录在数据库上进行的所有事务。 如果发生系统故障,意外删除,审计,时间点还原,恢复或设置高可用性,则需要事务日志以使数据库联机。
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()和fn_dump_dblog())读取记录的事务详细信息。 这使我们可以选择读取事务文件中的每个已记录数据。 它也可以与准备恢复解决方案结合使用。 让我们了解一下如何使用fn_dbLog函数,确保选择您感兴趣的列。fn_dblog是一个有组织的事务数据集,用于查看事务日志的详细信息。 所有事务都以创建时的相同顺序结转,并且每个事务都与特定的LSN(日志序列号)相关联。
Syntax:
句法:
fn_dblog (@startLSN,@endLSN)
fn_dblog(@ startLSN,@ endLSN)
@startLSN : Beginning of the transaction LSN
@startLSN:事务LSN的开始
@endLSN: End of the transaction LSN
@endLSN:交易结束LSN
Let us use the following query to analyze the transaction log for the specific transaction.
让我们使用以下查询来分析特定事务的事务日志。
SELECT [Current LSN], [Operation], [Context], [Transaction ID], [Begin Time], [end time], [Log Record Length], [AllocUnitName], [Description]FROM fn_dblog (null, null)where [Transaction ID]='0000:000003a3'
You can find the type of the operation, begin date timestamp and end time and associate object using fn_dblog() function for the specific transaction. Let’s deep-dive into only those operations types that we are interested to capture using the SQL.
您可以使用fn_dblog()函数找到特定交易的操作类型,开始日期时间戳记和结束时间并关联对象。 让我们仅深入研究我们希望使用SQL捕获的那些操作类型。
Most commonly used operation types are:
最常用的操作类型是:
In this section, we are going to create dummy table and run through few DML operations (Insert, Delete and Update), and then query the log to see those statement. Let’s dissect the flow into sample SQL for better understanding.
在本节中,我们将创建虚拟表并执行一些DML操作(插入,删除和更新),然后查询日志以查看那些语句。 为了更好地理解,我们将其分解为示例SQL。
Query fn_dblog for the operation type LOP_INSERT_ROWS, LOP_MODIFY_ROWS and LOP_DELETE_ROWS to see what has happened with the all the objects
在fn_dblog中查询操作类型LOP_INSERT_ROWS,LOP_MODIFY_ROWS和LOP_DELETE_ROWS,以查看所有对象发生了什么
select [Current LSN], [transaction ID] tranID,[end time] endTime, AllocUnitId, operation, Context from ::fn_dbLog(null, null) where operation in ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')
Fetch only those transaction happened in the last 5 minutes. In this case, the interval is defined as 5 minutes. You can change as per the requirement
仅获取最近5分钟内发生的交易。 在这种情况下,间隔定义为5分钟。 您可以根据要求进行更改
select [Transaction ID] tranID, [End Time] endTime from ::fn_dbLog(null, null) where Operation = 'LOP_COMMIT_XACT' and [End Time]>=DateADD(mi, -5, Current_TimeStamp)
Here is the complete T-SQL that captures the details about how many rows were inserted, updated, or deleted alongside the time when the respective transaction committed
这是完整的T-SQL,它捕获有关在相应事务提交时插入,更新或删除了多少行的详细信息。
SELECT objectName, begintime, endtime, account, SUM(LOP_INSERT_ROWS) [Insert], SUM(LOP_MODIFY_ROW) [Update], SUM(LOP_DELETE_ROWS) [Delete]FROM( SELECT object_name(p.object_id) objectName, tlog.[Current LSN], T.begintime begintime, c.endTime endtime, tlog.Operation operation, T.account account FROM sys.objects so inner join sys.partitions p on p.object_id=so.object_id inner join sys.system_internals_allocation_units AU on p.partition_id=AU.container_id inner join( select [Current LSN], [transaction ID] tranID,[end time] endTime, AllocUnitId, operation, Context from ::fn_dbLog(null, null) where (operation in ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')) --and context not in ('LCX_PFS', 'LCX_IAM')) --or operation in('LOP_COMMIT_XACT','LOP_BEGIN_XACT') )tlog on tlog.AllocUnitId=AU.allocation_unit_id inner join ( select [Transaction ID] tranID, [End Time] endTime from ::fn_dbLog(null, null) where Operation = 'LOP_COMMIT_XACT' and [End Time]>=DateADD(mi, -1500, Current_TimeStamp) ) c on tlog.tranID = c.tranID inner join ( SELECT [Transaction ID] tranID, SUSER_SNAME ([Transaction SID]) AS account, [Begin Time] as begintime FROM fn_dblog (NULL, NULL) WHERE [Operation] = N'LOP_BEGIN_XACT') T on tlog.tranID = T.tranID WHERE so.type='U')Xpivot( COUNT(operation) FOR operation in (LOP_INSERT_ROWS, LOP_MODIFY_ROW,LOP_DELETE_ROWS,LOP_ABORT_XACT,LOP_COMMIT_XACT))pGROUP BY objectName,begintime,endtime,account
Analyzing the table, we can see that the type of transaction carried out on each user object of the database, by pivoting we can measure, how many rows were inserted, updated, or deleted alongside the time when the respective transaction committed.
通过分析该表,我们可以看到,通过枢轴旋转,我们可以测量在数据库的每个用户对象上执行的事务类型,以及相应事务提交的时间以及插入,更新或删除了多少行。
Let us simulate the reading transaction log file using T-SQL with the process known as continuous auditing and schedule a job to run as close to 5 minutes intervals using SQL Server Agent.
让我们使用称为连续审核的过程使用T-SQL模拟读取事务日志文件,并使用SQL Server Agent将作业调度为以近5分钟的间隔运行。
In this section, we’ll outline the steps required to capture the transaction type information and scheduled a job to run the Stored Procedure at every 5 minutes. It can also be done without creating a stored procedure.
在本节中,我们将概述捕获事务类型信息并安排作业每5分钟运行一次存储过程所需的步骤。 也可以在不创建存储过程的情况下完成此操作。
Let us walk through the entire process using AdventureWorks2016 database.
让我们使用AdventureWorks2016数据库浏览整个过程。
Create a table to log the transaction details
创建一个表来记录交易明细
CREATE TABLE SQL_TLOG_Operation(ObjectName NVARCHAR(100),BeginTime datetime,EndTime datetime,InsertCnt int,UpdateCnt int,DeleteCnt int,Acccount varchar(100),LogDate datetime default getdate())
Prepare insert SQL statement
准备插入SQL语句
INSERT INTO SQL_TLOG_Operation(ObjectName,EndTime,InsertCnt,UpdateCnt,DeleteCnt)EXEC Audit_TransactionDetail 5
Type in the Job Name. In this case its SQL_LOG_Operation
输入工作名称。 在这种情况下,其SQL_LOG_Operation
Enter the T-SQL procedure call
输入T-SQL过程调用
In the Daily frequency section, choose to run every 5 minutes
在“每日频率”部分,选择每5分钟运行一次
I have performed various DML operations to see how the operations are getting tracked in log file using the following SQL
我已经执行了各种DML操作,以查看如何使用以下SQL在日志文件中跟踪这些操作
USE AdventureWorks2016GOSELECT * FROM SQL_TLOG_Operation
Thus far, we’ll discussed basics of transaction log, importance of log file, how to read transaction log to measure transaction rate of each object. The table can be further queried to get more granular report. Also, discussed how easy is to find those affected objects against these transactions using fn_dblog() function. It is also very useful technique to measure activities like page splits or objects being dropped etc:-.
到目前为止,我们将讨论事务日志的基础知识,日志文件的重要性,如何读取事务日志以测量每个对象的事务速率。 可以进一步查询该表以获得更详细的报告。 此外,还讨论了使用fn_dblog()函数针对这些事务查找那些受影响的对象有多么容易。 这也是衡量活动(例如页面拆分或对象被丢弃等)的非常有用的技术:-。
That’s all for now…
目前为止就这样了…
Stored procedure
存储过程
CREATE PROCEDURE Audit_TransactionDetail (@minutes int)ASBEGINSELECT objectName, begintime, endtime, account, SUM(LOP_INSERT_ROWS) [Insert], SUM(LOP_MODIFY_ROW) [Update], SUM(LOP_DELETE_ROWS) [Delete]FROM( SELECT object_name(p.object_id) objectName, tlog.[Current LSN], T.begintime begintime, c.endTime endtime, tlog.Operation operation, T.account account FROM sys.objects so inner join sys.partitions p on p.object_id=so.object_id inner join sys.system_internals_allocation_units AU on p.partition_id=AU.container_id inner join( select [Current LSN], [transaction ID] tranID,[end time] endTime, AllocUnitId, operation, Context from ::fn_dbLog(null, null) where (operation in ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')) --and context not in ('LCX_PFS', 'LCX_IAM')) --or operation in('LOP_COMMIT_XACT','LOP_BEGIN_XACT') )tlog on tlog.AllocUnitId=AU.allocation_unit_id inner join ( select [Transaction ID] tranID, [End Time] endTime from ::fn_dbLog(null, null) where Operation = 'LOP_COMMIT_XACT' and [End Time]>=DateADD(mi, -1500, Current_TimeStamp) ) c on tlog.tranID = c.tranID inner join ( SELECT [Transaction ID] tranID, SUSER_SNAME ([Transaction SID]) AS account, [Begin Time] as begintime FROM fn_dblog (NULL, NULL) WHERE [Operation] = N'LOP_BEGIN_XACT') T on tlog.tranID = T.tranID WHERE so.type='U')Xpivot( COUNT(operation) FOR operation in (LOP_INSERT_ROWS, LOP_MODIFY_ROW,LOP_DELETE_ROWS,LOP_ABORT_XACT,LOP_COMMIT_XACT))pGROUP BY objectName,begintime,endtime,account END
翻译自:
fn_dblog
转载地址:http://uznwd.baihongyu.com/