Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Which Columns Are Being Updated
Published: Dec 26, 2014
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 ,
col2 tinyint,
col3 tinyint,
col4 tinyint,
col5 tinyint,
col6 tinyint,
col7 tinyint,
col8 tinyint,
col9 tinyint,
col10 tinyint

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
primaryKeyValue tinyint,
updateTime datetime,
appName varchar(100),
userName varchar(100),
updateBitMask varbinary(32)

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

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

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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.