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, 2017 at 14:53
  • Yes. Thanks for bringing that up! I'll modify my question to specify.
    – Eric Cobb
    Apr 19, 2017 at 15:00
  • 1
    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'. Apr 19, 2017 at 15:07

2 Answers 2

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, 2017 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, 2017 at 17:21
0

For anyone who doesn't have the SQLAudit functionality from this answer available, and is doing this kind of thing purely with Extended Events: there are more events you should be watching than just the two listed in the question.

I use this list of events as my starting point:

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

There may be some overlap between these, but I don't overthink it; I'd rather see multiple relevant events than none.

In particular, the table updates I'm troubleshooting right now are all coming from sp_statement_starting and rpc_starting events, neither of which the question writer was catching.

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.