947
Chapter 41: Data Change Tracking and Capture
41
Change Tracking may also be confi gured 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 confi guration 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 per-
mission is required to enable the database for Change Tracking.Auto Cleanup
Change Tracking can create a lengthy audit trail, but it can also optionally automatically
clean up old Change Tracking data. The retention period can be set to any number of Days,
Hours, or Minutes. The default is to retain the data for two days (which is probably too
short for most applications).Auto_cleanup and the retention period can be set when Change Tracking is initially
enabled, or it can be modifi ed later by reissuing the set Change_Tracking option with
the new retention settings. In this situation, because Change Tracking is already enabled,
re-enabling Change Tracking would generate an error. It’s only necessary to change the
option setting:ALTER DATABASE [AdventureWorks2012]
SET change_tracking (change_retention = 7 days)Change Tracking doesn’t know when synchronizations occur. If the synchronization appli-
cation doesn’t run before the retention period expires, then the changes will not be seen by
the synchronization. Meaning that by default, you’d have to re-sync everything if you had
been disconnected for the weekend.Best Practice
Estimate the longest possible period between synchronizations and then triple that time to set your
retention time. Other than the disk space usage, there’s no risk in a longer retention period, but there’s
considerable risk in a retention period that’s too short.c41.indd 947c41.indd 947 7/31/2012 10:17:20 AM7/31/2012 10:17:20 AM
http://www.it-ebooks.info