05 September 2014

Enable Change Tracking - SQL Server 2008



Setup.

  1. First enable Change tracking. Here’s some sample T-SQL you can execute to start the process

ALTER DATABASE SAMPLEDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON);

GO

After it’s enabled and you want to check on which databases are affected, run this T-SQL

SELECT DB_NAME(database_id) [mydbname]
FROM SYS.CHANGE_TRACKING_DATABASES;

GO
To enable change tracking on a specific table use a query like this:
ALTER TABLE dbo.MYTABLENAME
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);
GO
Now to check the tables that have tracking enabled, you can get that list here:
SELECT OBJECT_NAME(OBJECT_ID) [MYTABLENAME]
FROM SYS.CHANGE_TRACKING_TABLES;
GO
No changes are required to statements for insert or update statements that are enabled with change_tracking.
To find out what’s changed after enabled, you can run a query similar to the following
SELECT ct.ID, ct.SYS_CHANGE_OPERATION, c.[NAME]
FROM CHANGETABLE(CHANGES dbo.MYTABLENAME, 0) ct
JOIN dbo.MYTABLENAME c ON c.[ID] = ct.[ID];

GO