Sql-server – Monitor source SQL code of inserts into table

monitoringsql servertrigger

I have a table with rows which should not be there, means the data is wrong according to business logic. And there is a lot of possible sources the insert statement of the "bad" row is coming from. I'm not able to repro this it's just happening from time to time. And I wanna know which exact SQL statement and module (eg. procedure/trigger / ad-hoc statement) is the source of that insert. To catch the culprit.

Using Profiler or Extended Events is not the way – because you don't see the data (just statements) and while I'm not able to repro this I cannot correlate the output with the data.

I've tried collect info from sys.dm_exec_input_buffer(@@SPID,0) inside my after triggering on insert (made for logging purposes, collecting every insert in that table). But this is too wide. It's giving just initial SQL statement, let say outer stored procedure, but the insert statement could be invoked in some nested stored procedure or trigger.

I've tried to collet output of sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) – see the whole code below. But this is too narrow in opposite – it is giving me statement of my collecting after trigger.

Is there a way to catch something like it?

SELECT 
OuterSQL = CAST(T.text as NVARCHAR(255))
,InnerSQL = CAST((SUBSTRING(t.text,(s.statement_start_offset/2) + 1, 
          (CASE
             WHEN s.statement_end_offset = -1 
               THEN DATALENGTH(t.text)
             ELSE s.statement_end_offset
           END - s.statement_start_offset)/2 + 1)) as NVARCHAR(255))
FROM sys.dm_exec_connections
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T
    INNER JOIN sys.dm_exec_query_stats s ON sys.dm_exec_connections.most_recent_sql_handle = s.sql_handle
WHERE session_id = @@SPID;

Best Answer

You haven't included version or edition info, so if you're running SQL Server 2016 SP1 or higher (or Enterprise Edition for earlier versions), try using SQL Server Audit to trace this information.

You can set up a Database-level Audit to capture INSERT action on that table, then query the audit files to identify the calling application and statements.

SQL Audit: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

Audit Action Groups: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017

Also, Profiler and Extended Events should be able to capture this information for you. Which trace events are you capturing?

If you use the template TSQL_Replay trace and filter it appropriately, you should be able to see the calling code, including the explicit values passed to the stored proc or INSERT statement.

Related Question