Microsoft SQL Server 2012 Bible

Configuring Change Tracking

Compared to other optional SQL Server technologies, Change Tracking is relatively easy to turn on and configure. It's simply turned on for the database and then for each table.

Enabling the Database

Change Tracking may be enabled for the database in Object Explorer's Database Properties dialog, available from each database's context menu (as shown in Figure 41.1). Changing the values in the drop-down boxes immediately changes the database settings when you close the properties dialog.

Figure 41.1 The Database Properties' Change Tracking page displays the current settings and may be used to enable or disable Change Tracking.

Change Tracking may also be configured with T-SQL using SQL ALTER SET:

ALTER DATABASE AdventureWorks2012
SET Change_tracking = on
(change_retention = 24 hours,
auto_cleanup = on);

The current Change Tracking database configuration can be viewed in the Object Explorer Database Properties dialog, or by querying the sys.change_tracking_databases DMV:

SELECT d.name, ct.is_auto_cleanup_on, ct.retention_period,
  ct.retention_period_units, ct.retention_period_units_desc
 FROM sys.change_tracking_databases ct
  JOIN sys.databases d
   ON ct.database_id= d.database_id;

The database must be in 9.0 Compatibility Mode or higher, and at least db_owner role permission is required to enable the database for Change Tracking.

Auto Cleanup

Change Tracking ...