Enable Change Tracking on Database and Table

The two ALTER statements that turn on change tracking for a database and for an individual table.

Published Aug 8, 2009

Change tracking is enabled in two steps. First at the database level - setting the retention period (how long change versions are kept) and whether cleanup happens automatically - and then per table, optionally with TRACK_COLUMNS_UPDATED so you can tell not just that a row changed but which columns did.

ALTER DATABASE <Datebase Name>
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)
GO

ALTER TABLE <Schema Name>.<Table Name>
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = <ON,OFF>)
GO