Change Tracking in SQL Server 2008
SQL Server 2008 includes several inbuilt
features for Auditing – namely Change Data Capture (CDC) and Auditing and
Change Tracking – this article we are going to discuss Change Tracking.
What is Change Tracking
Change tracking is a lightweight solution
that provides an efficient change tracking mechanism for applications.
In case of data warehousing you will need
to extract data from different data sources. If you need to extract data from a
large table it is necessary to extract only the changes. If you don’t have a
dedicated mechanism either you will have to extract all the data once again, implement
triggers or else implement a timestamp mechanism. All of these are difficult
to implement and may not be scalable. There are other solutions such as Slowly
Changing Dimensions in SSIS, Change Data Capture in SQL Server. Compared
with SSIS and CDC, Change Tracking is a lightweight solution to extract
modified data from one SQL Server to another SQL Server .
Configuring Change Tracking
To enable change tracking for a table, you will
need to enable it on the database first which can be done in two ways.
The below T-SQL script enables Change
tracking in a DB_TRACK database:
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 3 DAYS,
AUTO_CLEANUP = ON)
There are two optional parameters in the above
query – CHANGE_RETENTION and AUTO_CLEANUP. CHANGE_RETENTION allows you to
configure the period for which tracking data should be kept, ( 2 days by
default) data which are older will be removed automatically. Enabling the AUTO_CLEANUP
option will enables the cleanup task of removing old data, which is ON by
default. You can change those parameters at any given time after enabling the
The following T-SQL enables change
tracking with default values:
SET CHANGE_TRACKING = ON
Set the change tracking to off using the
SET CHANGE_TRACKING = OFF
Note – you are not allowed to set change
tracking to off unless you have disabled all the table for change tracking.
Similarly, you can use SQL Server
Management Studio (SSMS) to enable change tracking by following the below
1. Right click the database that you want to enable change tracking on.
Select properties from the context menu
Select the Change Tracking option (see below screenshot)
You can set the relevant
parameters from this page to set the Change Tracking on.
After configuring Change
Tracking, you can access that information by querying a system view:
SELECT * FROM
Above query will give
information about the entire database which has Change Tracking enabled.
You will need to have at least dbcreator server
role to enable Change Tracking.
Database compatibility should be 90 or greater
to enable Change Tracking (you can enable Change Tracking in a database of lesser
compatibility but some change tracking functions will return errors.)
Using snapshot isolation is the easiest way for
you to help ensure that all change tracking information is consistent. For this
reason, it is recommend that snapshot isolation be set to ON for the database
by executing below command. Snapshot isolation specifies that data read by any
statement in a transaction will be a consistent version of the data that
existed at the start of the transaction. The transaction can only recognize
data modifications that were committed before the start of the transaction.
Data modifications made by other transactions after the start of the current
transaction are not visible to statements executing in the current transaction.
The overall effect is the statements in a transaction get a snapshot of the
committed data as it existed at the start of the transaction.
ALTER DATABASE [DB_Track]