主页 / dba / 问题 / 171406
Accepted
Eric Cobb
Asked: 2017-04-20 06:46:09 +0800 CST

使用扩展事件跟踪表的更新

  • 772

我有一张不断更新的表格，没有人能弄清楚更新的来源。我怀疑它们来自实体框架，但我想通过扩展事件捕获更新和相关信息来证明这个理论。

我在 SQL 2014 Enterprise 上，并尝试使用 exec_prepared_sql 和 sql_statement_starting 事件来捕获该信息。这是我到目前为止所拥有的：

CREATE EVENT SESSION [Query Trace] ON SERVER 
ADD EVENT sqlserver.exec_prepared_sql(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123))),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123)))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\Query-Trace.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

这似乎运作良好，我已经对其进行了测试，并且能够捕获常规 UPDATE 语句和使用 sp_executesql 调用的语句。但是，我仍然缺少一些东西。表中的数据仍在修改中，并且此扩展事件未捕获执行此操作的 UPDATE。

所以，我的问题是：

1) 为了捕获可能来自实体框架的更新，我还需要在扩展事件中观看其他内容吗？

2）我应该使用其他东西来代替扩展事件吗？

谢谢！

sql-server sql-server-2014

2 个回答

  1. Best Answer

    在使用 SQL Server Enterprise Edition（或运行任何高于 2016 SP1 的版本）时，我建议使用 SQLAudit 功能。这将为您提供一些关于谁在触摸您的桌子以及正在执行的命令的详细信息。

    对于这样的事情，您可能希望使用数据库审计规范和服务器审计（用于定义审计将写入的位置），然后将其范围缩小到要监视的表。然后，将更改范围限定为公共角色，您将捕获发生的所有更改。

    该脚本应该可以让您到达那里（在您的开发环境中进行测试，并替换您要监视的表的相关部分）。

    USE [master]

GO

CREATE SERVER AUDIT [Audit-TblChanges]
TO FILE 
(   FILEPATH = N'C:\SQLAudit'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

GO

USE [YourDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable]
FOR SERVER AUDIT [Audit-TblChanges]
ADD (UPDATE ON OBJECT::[YourTable] BY [public]),
ADD (INSERT ON OBJECT::[YourTable] BY [public])

GO


USE [master]
GO
ALTER SERVER AUDIT [Audit-TblChanges] WITH (STATE = ON);

USE [YourDatabase]
GO
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable] WITH (STATE = ON);
GO

    您可以使用 GUI 快速读回数据，或使用sys.fn_get_audit_file直接从 SQL Server 查询数据。

    • 9

  2. 对于没有此答案中的 SQLAudit 功能并且纯粹使用扩展事件来做这种事情的任何人：除了问题中列出的两个之外，您应该观看的事件更多。

    我使用这个事件列表作为我的起点：

    1. exec_prepared_sql
    2. sql_statement_starting
    3. sp_statement_starting
    4. rpc_starting
    5. sql_batch_starting

    这些之间可能有一些重叠，但我并没有想太多；我宁愿看到多个相关事件也不愿看到一个。

    特别是，我现在正在排除故障的表更新都来自事件，问题作者都没有捕捉到这些事件sp_statement_starting。rpc_starting

    • 1

相关问题