Which Columns Are Being Updated
Published: 28th September 2013
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
go

create table updateTrack
(
  
col1 tinyint primary key clustered ,
  
col2 tinyint,
  
col3 tinyint,
  
col4 tinyint,
  
col5 tinyint,
  
col6 tinyint,
  
col7 tinyint,
  
col8 tinyint,
  
col9 tinyint,
  
col10 tinyint
)
go

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
go


Now we’ll create a tracking table to store some information about our updates…

if object_id('updateTrackLog') is not null drop table updateTrackLog
go

create table updateTrackLog
(
  
primaryKeyValue tinyint,
  
updateTime datetime,
  
appName varchar(100),
  
userName varchar(100),
  
updateBitMask varbinary(32)
)
go


Finally we’ll add our trigger to the main table:

create trigger tr_updateTracking on updateTrack for update
as
   insert into
dbo.updateTrackLog
  
select col1, current_timestamp, app_name(), suser_sname(), columns_updated()
  
from inserted
go


Now let’s run a quick update and see what output we get:

update updateTrack
set col5 = 5, col8 = 8
where col1 = 2

select *
from updateTrackLog


Okay… so now we have our tracking in place, we need to decode the bitmask:

select column_name
from information_schema.columns
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.
Comments:
NB: Comments will only appear once they have been moderated.