Anthony Brown

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,

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

declare @sql varchar(100)

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