Which tables are using Change tracking

Question

My backup is failing: Failed to flush the commit table to disk in dbid 12 due to error 2601. Check the errorlog for more information.

my database has change tracking enabled. How can i determine which tables have it enables? i have looked at 50+ and can't locate the ones with it on. sql 2008 r2

Thanks

Answer 1

To find the tables that have Change Tracking enabled, execute the following script against the tracked database

SELECT s.name AS Schema_name, t.name AS Table_name 
FROM sys.change_tracking_tables ctt
JOIN sys.tables t 
    ON t.object_id = ctt.object_id
JOIN sys.schemas s
    ON s.schema_id = t.schema_id
ORDER BY s.name, t.name

You can find more details about helpful system views for Change Tracking here:

sys.change_tracking_tables (Transact-SQL)

sys.change_tracking_databases (Transact-SQL)

Answer 2

Here's a query by Brent Ozar that gives you a list of the Hidden Tables maintained by Change Tracking to store the changes, the tables involved in CT the rows and space taken up in those hidden tables.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

SELECT 
   sct1.name AS CT_schema,
   sot1.name AS CT_table,
   ps1.row_count AS CT_rows,
   ps1.reserved_page_count*8./1024. AS CT_reserved_MB,
   sct2.name AS tracked_schema,
   sot2.name AS tracked_name,
   ps2.row_count AS tracked_rows,
   ps2.reserved_page_count*8./1024. AS tracked_base_table_MB,
   change_tracking_min_valid_version(sot2.object_id) AS min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 ON it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 ON sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 ON it.object_id = ps1. object_id AND ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 ON it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 ON sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 ON sot2.object_id = ps2. object_id AND ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210);
GO