7

I have a table that keeps getting updated, and no one can figure out where the updates are coming from. I suspect they are coming from Entity Framework, but I want to capture the UPDATE and associated information via an Extended Event to prove this theory.

I'm on SQL 2014 Enterprise, and trying to capture that information using the exec_prepared_sql and sql_statement_starting events. Here is what I have so far:

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

This seems to work well, I have tested it and been able to captured both a regular UPDATE statement and one called with sp_executesql. However, I am still missing something. The data in the table is still getting modified and this Extended Event is not capturing the UPDATE that does it.

So, my questions are:

1) Is there something else that I need to be watching in my Extended Event in order to capture UPDATES possibly coming from Entity Framework?

2) Is there something else I should be using instead of Extended Events for this?

Thanks!

3
  • Are you running Enterprise Edition SQL? – Nic Apr 19 '17 at 14:53
  • Yes. Thanks for bringing that up! I'll modify my question to specify. – Eric Cobb Apr 19 '17 at 15:00
  • I would add sp_statement_starting event. Because 'sql_statement_starting' will only give you the call of the stored procedure which might be more than one (or none) insert statement. To see each one you need 'sp_statement_starting'. – SqlWorldWide Apr 19 '17 at 15:07
9

When using SQL Server Enterprise Edition (or running any edition newer than 2016 SP1) I would recommend going with SQLAudit functionality. This will give you some great granualar information about who is touching your tables, and the commands that are being executed.

For something like this you would want to use a database audit specification along with a server audit (used to define where your audit will write to), and then scope it down to the table that you want to monitor. By then scoping changes to the public role you will capture any and all changes that happen.

This script should get you there (test in your dev environment, and replace the relevant pieces for the table(s) that you want to monitor).

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

You can quickly read the data back using the GUI, or use sys.fn_get_audit_file to query the data directly from SQL Server.

2
  • Is this auditing only available in Enterprise? – cloudsafe May 30 '17 at 16:00
  • 1
    Database level auditing is available in Enterprise/Developer Edition only prior to SQL Server 2016 SP1. They have changed with the SP1 release to allow database auditing in all editions (edited the answer to include this new information) – Nic May 30 '17 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.