If you turned off automatic cleaning, it’s best for you to periodically go through and delete the change tracking information yourself, by disabling and re-enabling change tracking for each table. Otherwise, yes, tracking data will continue to grow and grow.
You cannot directly access base tables, but you can poke their metadata. The following query shows the relative number of rows:
select s.name as schema_name , t.name as table_name , (select sum(rows) from sys.partitions x where o.parent_object_id = x.object_id) as rows_in_base_table , o.name as tracking_table , p.rows as rows_in_tracking_table from sys.objects o join sys.tables t on o.parent_object_id = t.object_id join sys.schemas s on t.schema_id = s.schema_id join sys.partitions p on o.object_id = p.object_id where o.name like 'change[_]tracking%' and o.schema_id = schema_id('sys') order by schema_name, table_name
Run this in your database and you should get a rough understanding of current overhead.
Change tracking tables follow the standard layout. For example:
select c.name, c.column_id , type_name(user_type_id) as type_name , c.max_length, c.precision, c.scale , c.is_nullable, c.is_identity from sys.columns c where object_id = ( select top 1 object_id from sys.objects o where o.name like 'change[_]tracking%' and o.schema_id = schema_id('sys') )
Columns k_% differ in the table and correspond to the main keys of the table being tracked. You study the basic minimum overhead of 18 bytes + (primary key length) for each row. It adds!
For example, I keep track of a few skinny base tables just 15 bytes wide with a 7-byte composite key. This makes tracking tables 18 + 7 = 25 bytes wide!
Peter Radocchia
source share