Pages

Friday, September 9, 2022

Change Tracking cleanup doesn't work - negative cleanup_version / change_tracking_hardened_cleanup_version

So you have enabled the Change Tracking with AUTO_CLEANUP = ON,
but the CT cleanup doesn't work, no records deleted from the side tables
and from sys.syscommittab system table after the retention period passed.

When you run: 

-- Delete the data in sys.committab
exec sys.sp_flush_commit_table_on_demand @numrows  = 1000

you get negative value/number in change_tracking_hardened_cleanup_version 

The value returned by change_tracking_hardened_cleanup_version() is -XXXXXXXXX

Or you can see that the cleanup_version is negative from this query:

select sc.name as tracked_schema_name,
so.name as tracked_table_name,
ctt.cleanup_version
from sys.change_tracking_tables as ctt
inner join sys.objects as so on ctt.[object_id]=so.[object_id]
inner join sys.schemas as sc on so.schema_id=sc.schema_id
where so.name ='XXX-your-table-name-XXX'
go

Also when you run:

exec sp_flush_CT_internal_table_on_demand 

you see Total rows deleted: 0. in the output

If so, there is a fix from Microsoft, see it here.
Also you will need to enable trace flag 8290 and let the auto cleanup process run.

DBCC TRACEON( 8290, -1)
go
DBCC TRACESTATUS(8290)
go

Finally, you can see history of Change Tracking cleanups in the table below:

select * from dbo.MSchange_tracking_history

No comments:

Post a Comment