2

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;
  • Is there some reason the AFTER TRIGGER cannot verify the integrity of the data and raise an error (rollback) the transaction if the data is bad? You will find out where the failure is occurring fairly quickly. – Scott Hodgin Sep 17 '18 at 14:09
  • That's a logical thought but unfortunately business logic is more complicated and I'm not able to verify data purity at the time of insertion :-( – jerik1 Sep 17 '18 at 14:32
1

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.

  • It's MS SQL Server 2017. According to my tests neather Audit nor Profiler trace will return any identification of the inserted row (eg. identity or PK value). So the only & vague way to correlate it is existence of some time stamp. – jerik1 Sep 17 '18 at 13:13
-1

I am not really sure what you mean by bad rows, Is it something which is junk data coming from external source or from some internal deliberate attempt. In any case, I think you need to be careful about granted access to users at your database. Most of users should be having read access only so that they can't perform any DML operation i.e. insert/update/delete. For those users, who are supposed to be performing DML operations, should be doing it through execute permission on stored procedure/functions and not through direct write access on tables.

I am not sure how is your application architecture however above explains the ideal(effective) way of handling business logic and avoid any issue with bad/wrong data inserts. Sysadmin access will be only with DBA who will not know much about data and their work is more on administration side.

Having said above, if its external party then get your firewalls checked by network administrator, in case its internal - check the access. And if this is coming from legitimate application then raise this with application team and get the business validation in place at application layer instead of finding the source of data after insert at database.

One more thing to note here - if bad data is coming through any user object i.e. procedure/trigger/function that means they need to be enhanced and business logic needs to be in place to error out when data is not updated/inserted in-line with business logic.

I hope above helps.

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.