Sequal Server.Com

1. Purpose of activity:
The Purpose of this activity is to improve technical knowledge in DBA team.

2. SQL 2008 – CHANGE TRACKING FEATURE
Change tracking is a new feature in SQL Server 2008 that allows applications to query for information about the changes that have been made to user tables since a previous point in time.

3. Enabling Change Tracking at the database level

a) Open the SQL Server Management Studio and connect to SQL Server 2008
b) Open Object Explorer and expand Databases.
c) Select the particular database where the change tracking needs to be enabled. Right click the database and select properties to launch the properties dialog.
d) Navigate to “Change Tracking” page and change the value of change tracking from false to true





e) Enter the required values for Retention Period, Retention Period Units and Auto Cleanup.
- Retention Period: Change information will be retained for atleast the time period that is specified by the retention period and retention period units.
- Retention Period Units: The units of the Retention Period Days or Months or Years.
- Auto Cleanup: Automatically clean up the change tracking information by using the specified retention period. If this value is false, change tracking information automatically continues to grow.

4. Enabling Change Tracking at the table level

a) Select the particular table where the change tracking needs to be enabled. Right click the table and select properties to launch the properties dialog.
b) Navigate to “Change Tracking” page and change the value of change tracking from false to true.



c) Enter the required value for the track columns updated field. This field is used to indicate the columns which are changed by UPDATE operation and also indicates that row has changed.
Note: Change tracking is supported only on the tables having primary key.

5. VIEW CHANGES & TSQL SCRIPTS
In order to find the rows changed on a table, we needs to use CHANGETABLE(CHANGES …)and CHANGE_TRACKING_CURRENT_VERSION().
TSQL SCRIPTS
DECLARE @sync_version int;
DECLARE @last_sync_version int;
SET @sync_version = CHANGE_TRACKING_CURRENT_VERSION();

– Obtain incremental changes using the sync version obtained last time
SELECT
P.,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES dbo.d, 0) AS CT
ON
P.c1 = CT.c1
CHANGETABLE() returns the table containg the below columns :
SYS_CHANGE_OPERATION – Type of DML operation on a row (ie I, U, D)
SYS_CHANGE_COLUMNS – Columns changed from a baseline version
SYS_CHANGE_CONTEXT – Optionally specified with the WITH clause
SYS_CHANGE_VERSION – Current change version associated with a row.
Note: we can only view changes through Query Analyzer. We can’t view in GUI mode.