It isn't uncommon for us to receive autocleanup or manual cleanup issues regarding Change Tracking in CSS. If you have very busy tables that results in a lot of data being written to the side tables, it isn't uncommon to see error 22122 in the SQL Server error log indicating there was a failure in running cleanup and we will retry later.
2022-04-07 17:30:29.13 spid42s Error: 22122, Severity: 16, State: 1.
2022-04-07 17:30:29.13 spid42s Change Tracking autocleanup failed on side table of "SourceTableName". If the failure persists, use sp_flush_CT_internal_table_on_demand to clean up expired records from its side table.
If the issue persists and we stack a lot of data that needs to be removed which can be too much for autocleanup to manage. At that point, we recommend running (as is indicated in the error message) sp_flush_CT_internal_table_on_demand which is discusseed in detailed in Amit's blog post Change Tracking Cleanup–Part 1.
I will not discuss the internals of the process as Amit Banerjee already touched on this in the referenced blog. However if you are routinely encountering issues where autocleanup can't keep up and/or manual cleanup is taking significant time, you can add the index and see if it helps and I have the process enumerated below. Keep in mind this is not guaranteed to fix all cleanup issues and it may still be necessary to run manual cleanup regularly. If you continue to have issues with cleanup feel free to create a ticket with us here at CSS to assist you and get specific data to your environment.
How to Create si_commit_time Index
Some things to know before we start:
How to do it:
/* Example below is using Windows Authentication (-E parameter), for the default instance on server WIN-L3UUVD9S3HV (-S parameter) and using DAC (-A parameter) */ sqlcmd -E -S WIN-L3UUVD9S3HV -A
USE [ChangeThisToYourDBName] GO CREATE NONCLUSTERED INDEX [si_commit_time] ON [sys].[syscommittab] ( [commit_time] DESC, [commit_ts] DESC ) WITH (SORT_IN_TEMPDB = ON) GO
USE [ChangeThisToYourDBName] GO SELECT object_name(object_id) tablename,index_id,name as index_name,type_desc FROM sys.indexes where object_id=object_id('sys.syscommittab') and name = 'si_commit_time' GO
Hope this helps!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.