Change Tracking Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.change_tracking_databases
• sys.change_tracking_tables

sys.change_tracking_databases

database that has change tracking enabled.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
database_id		•	•	•	int	ID of the database. This is unique within the instance of SQL Server.
is_auto_cleanup_on		•	•	•	bit	Indicates whether change tracking data is automatically cleaned up after the configured retention period: 0 = Off 1 = On
retention_period		•	•	•	int	If autocleanup is being used, the retention period specifies how long the change tracking data is kept in the database.
retention_period_units_desc		•	•	•	nvarchar(60)	Specifies the description of the retention period: Minutes Hours Days
retention_period_units		•	•	•	tinyint	Unit of time for the retention period: 1 = Minutes 2 = Hours 3 = Days

TSQL

Sql 2008
SELECT [database_id], [is_auto_cleanup_on], [retention_period], [retention_period_units_desc], [retention_period_units] FROM sys.change_tracking_databases
Sql 2008 R2
SELECT [database_id], [is_auto_cleanup_on], [retention_period], [retention_period_units_desc], [retention_period_units] FROM sys.change_tracking_databases
Sql 2012
SELECT [database_id], [is_auto_cleanup_on], [retention_period], [retention_period_units_desc], [retention_period_units] FROM sys.change_tracking_databases

Back to Top

---

sys.change_tracking_tables

table in the current database that has change tracking enabled.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
object_id		•	•	•	int	ID of a table that has a change journal. The table can have a change journal even if change tracking is currently off. The table ID is unique within the database.
is_track_columns_updated_on		•	•	•	bit	Current state of change tracking on the table: 0 = OFF 1 = ON
begin_version		•	•	•	bigint	Version of the database when change tracking began for the table. This version is usually indicates when change tracking was enabled, but this value is reset if the table is truncated.
cleanup_version		•	•	•	bigint	Version up to which cleanup might have removed change tracking information.
min_valid_version		•	•	•	bigint	Minimum valid version of change tracking information that is available for the table. When obtaining changes from the table that is associated with this row, the value of last_sync_version must be greater than or equal to the version reported by this column. For more information, see CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)1.

TSQL

Sql 2008
SELECT [object_id], [is_track_columns_updated_on], [begin_version], [cleanup_version], [min_valid_version] FROM sys.change_tracking_tables
Sql 2008 R2
SELECT [object_id], [is_track_columns_updated_on], [begin_version], [cleanup_version], [min_valid_version] FROM sys.change_tracking_tables
Sql 2012
SELECT [object_id], [is_track_columns_updated_on], [begin_version], [cleanup_version], [min_valid_version] FROM sys.change_tracking_tables

Back to Top

---