This came about as a result of a series of mass updates being applied to a replicated table. This series of updates was causing replication to lock up and become so far behind that the only acceptable course of action was a full rebuild. This is by far and away the course of action I least like taking, but in this example it was unavoidable.
The problem was that we had no clue as to the underlying cause. All developers were certain that any recent changes would not have had this effect and therefore we were in the dark. The only way to get to the bottom of it was to track which columns were being updated and by what in order to verify whether these updates were valid and necessary.
To do this we needed to find a way to track all changes that was unobtrusive, would tell us exactly what columns were being modified, and by which process. This is possible with Change Data Capture, but that can be too heavy on the system, is an Enterprise only feature, and was also more than we required for our needs. I simply wanted to know what columns were being modified so that I could find the code responsible. The actual values wee of no consequence.
Luckily SQL Server provides a nice and clean way of doing this inside a trigger, so that’s what I’ll demonstrate below.
Firstly we’ll create a test table with 10 columns (simply named) and insert a couple of rows of data:
if object_id('updateTrack') is not null drop table updateTrack
create table updateTrack
col1 tinyint primary key clustered ,
insert into updateTrack select 1, 1, 1, 1, 1, 1, 1, 1, 1, 1
insert into updateTrack select 2, 2, 2, 2, 2, 2, 2, 2, 2, 2
Now we’ll create a tracking table to store some information about our updates…
if object_id('updateTrackLog') is not null drop table updateTrackLog
create table updateTrackLog
Finally we’ll add our trigger to the main table:
create trigger tr_updateTracking on updateTrack for update
insert into dbo.updateTrackLog
select col1, current_timestamp, app_name(), suser_sname(), columns_updated()
Now let’s run a quick update and see what output we get:
set col5 = 5, col8 = 8
where col1 = 2
Okay… so now we have our tracking in place, we need to decode the bitmask:
where table_name = 'updateTrack'
and sys.fn_IsBitSetInBitmask(0x9000,-- bitmask taken from our log table
columnProperty(object_id(table_schema + '.' + table_name), column_name, 'columnID')) != 0
Simple. Now you can use this information to combine your tracking table with this bitmask in order to know what fields are being updated, by who, and from which application. With that information it was easy for me to track down the offending piece of code and disable it until the developers could look to correct the issue.