主页 / dba / 问题 / 211729
Accepted
Yunus UYANIK
Asked: 2018-07-10 23:20:52 +0800 CST

如何通过sys.fn_dump_dblog获取用户信息

  • 772

我正在查看 sys.fn_dump_dblog，我可以看到记录的旧状态，但看不到哪些用户正在执行操作。SPID、事务 SID 列始终为空，如何修复它或任何选项？

我正在使用 SQL Server 2016。

我的查询：

SELECT
    *
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\LogBackup\blabla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (
[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (
([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))
)
AND [PartitionID] IN (72057595034533888)
)
OR ([operation] = 'LOP_HOBT_DDL')
sql-server transaction-log

2 个回答

  1. Best Answer

    通过参考@Kin，我已经解决了查询问题。

    SELECT
    SUSER_SNAME([Transaction SID]) UserName
    ,[Current LSN]
   ,[operation]
   ,[Context]
   ,[Transaction ID]
   ,[Transaction Name]
   ,[Begin Time]
   ,[End Time]
   ,[Flag Bits]
   ,[PartitionID]
   ,[Page ID]
   ,[Slot ID]
   ,[RowLog Contents 0]
   ,[Log Record]
   ,[RowLog Contents 1]
   INTO ##temp
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\balbla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (([operation] IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT'))
OR ([operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))))
OR ([operation] = 'LOP_HOBT_DDL'))


SELECT t2.UserName,t.operation,object_name(p.object_id),count(1) FROM ##temp t
LEFT JOIN ##temp t2 ON t.[Transaction ID]=t2.[Transaction ID] AND t2.UserName is not null
INNER JOIN sys.partitions p ON p.partition_id=t.PartitionId
WHERE t.[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
GROUP BY t2.UserName,t.operation,object_name(p.object_id)
    • 2

  2. 事务日志不是为了跟踪谁在您的数据库中做了什么而设计的。如果您需要审核 SQL Server 上的用户行为，请使用SQL Server 审核。

    审核SQL Server 数据库引擎实例或单个数据库涉及跟踪和记录数据库引擎上发生的事件。SQL Server 审计允许您创建服务器审计，其中可以包含服务器级事件的服务器审计规范和数据库级事件的数据库审计规范。审核的事件可以写入事件日志或审核文件。

    您也可以使用扩展事件，但正如 Nic 在这个答案中指出的那样，审计更适合这种情况。

    如果您不想使用审计（明确地说，我建议审计，因为它会解决您遇到的问题而无需手动读取事务日志），您可以在表上创建触发器来监视用户活动，但那是需要做更多的工作并且容易出现错误。

    使用审计。

    • 0

相关问题