13 October 2009 17:30
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 (
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
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