13 October 2009 17:30 antxxxx

How to find out what procedure is doing a data modification

Here is a little trick to find out what stored procedures or statements are causing data modifications. You could use a trace for this, and in most situations this will be preferable as you can filter what is captured, and get more information This is another method that uses a trigger and dbcc inputbuffer

First create a table to store the results

create table dbcc_monitor (

EventType nvarchar(60),

Parameters int,

EventInfo nvarchar(510) )


 

You then create a trigger on the table you want to monitor that runs dbcc inputbuffer for the current @@spid

 

create trigger some_table_trigger

on some_table for insert, update, delete

as

declare @sql varchar(100)

set nocount on

select @sql = 'dbcc inputbuffer(' + convert(varchar, @@spid) + ')'

insert into dbcc_monitor exec(@sql)

 

Then whenever an insert, update or delete happens on the table, the results of dbcc inputbuffer are logged to the dbcc_monitor table. This should contain the name of the stored procedure that caused the data modification. You could also capture the data from the inserted and deleted tables in the trigger to find out what the modifications were.

A word of warning though. There will be a performance hit with this as it logs all data modifications, so dont do it on a heavily modified table

Comments

# Handy SQL Server knowledge nuggets « C# Hacker – The Rambling Coder

Pingback from  Handy SQL Server knowledge nuggets «  C# Hacker – The Rambling Coder