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:
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