fn_dblog()和fn_full_dblog()的使用

SQL Server 2017中，函数fn_full_dblog()对函数fn_dblog()做了改进，允许用户读取事务日志中的活跃部分，检索对数据库修改活动的相关信息。

使用fn_dblog()要记住以下几点：

1.只是返回事务日志中活跃部分的内容，比如打开的事务、或上一个活动

2.这是一个文档中没有列出的函数，官方有权修改且不用通知用户。因此不建议将其嵌入生产的代码中

函数的定义和查询

fn_dblog()必须在数据库上下文中调用，需要两个参数：开始和结束的日志序列号(LSN)。如果都设置为null，表示检索整个日志。​

fn_db_log()
SELECT * FROM fn_dblog (
              NULL, -- Start LSN nvarchar(25)
              NULL  -- End LSN nvarchar(25)
       )

fn_full_dblog()
SELECT * FROM sys.fn_full_dblog 
 (
  NULL, -- Start LSN nvarchar (25) 
  NULL, -- End LSN nvarchar (25)  
  NULL, -- Database ID int 
  NULL, -- Page file ID int 
  NULL, -- Page ID int 
  NULL, -- Logical Database ID nvarchar (260)
  NULL, -- Backup Account nvarchar (260)
  NULL -- Backup Container nvarchar (260)
 )

如果你想传入指定的LSN，需要转换LSN的格式，将3个16进制的数字转换成varchar。

返回的列

fn_dblog()和fn_full_dblog()返回相同的列，共有130个列，以下是我测试中比较有用的列：

SID、XACTID和SPID只是出现在事务的开头或结尾。每个事务都有LOP_BEGIN_XACT和LOP_COMMIT_XACT(如果成功提交的话)，表示事务的开始和结束。​

--查看活跃的事务
SELECT * FROM sys.dm_tran_active_transactions;
SELECT * FROM sys.dm_tran_database_transactions;
DBCC OPENTRAN();
​
​
-- Get Log usage for a specific transaction
SELECT *
FROM fn_dblog(NULL,NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM fn_dblog(null,null) WHERE [Transaction SID] = 0x6163d86d97bd6a4da016bac4330f975f)
 
​
-- Get Log usage for a specific SPID
SELECT *
FROM fn_dblog(NULL,NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM fn_dblog(null,null) WHERE [SPID] = 78)
 
​
-- Get Log usage for a specific user specified transaction
SELECT *
FROM fn_dblog(NULL,NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM fn_dblog(null,null) WHERE [Xact ID] = 277094)
 
-- Get the active part of the LOG for a specific transaction
SELECT
       [Current LSN]
       ,[Previous LSN]
       ,[Operation]
       ,[Context]
       ,[Transaction ID]
       ,[Log Record Length]
       ,[AllocUnitName]
       ,[Page ID]
       ,[SPID]
       ,[Xact ID]
       ,[Begin Time]
       ,[End Time]
       ,[Transaction Name]
       ,[Transaction SID]
       ,[Parent Transaction ID]
       ,[Transaction Begin]
       ,[Number of Locks]
       ,[Lock Information]
       ,[Description]
       ,[Log Record]
FROM sys.fn_dblog(null,null)
WHERE [Transaction ID] = '0000:00011043'

Operations列

Operations表明操作的类型。整个列非常有用。以下是部分解释：

--See all operations and their size
SELECT
[Operation],count(*) AS [No of Records],
SUM([Log Record Length]/1024.00/1024.00) AS [RecordSize (MB)]
FROM fn_dblog(NULL,NULL)
GROUP BY Operation
ORDER BY [RecordSize (MB)] DESC

使用场景

使用fn_dblog()分析'SELECT … INTO'和'INSERT … SELECT'语句的差异。

从图中可以看到，借助使用fn_dblog()，比'INSERT … SELECT'语句比'SELECT … INTO'语句更昂贵，因为它是逐行插入。

第一个图中，LOP_INSERTED_ROWS结果是776317，使用100MB的日志；第二个图中，可以看到是逐行插入且都记入了日志。

另一方面，'SELECT … INTO'只是将页dump到新的表。LOP_FORMAT_PAGE是最昂贵的操作(41MB);但是看第二个图中每条日志记录的长度，是8KB，一个数据页的大小。