However, you can run the sp_flush_commit_table_on_demand sproc to manually delete CT records that are outside of the retention window. This appears to work well in my dev testing, but I've noticed that it only clears out records from the syscommittab table; it does not seem to prune the records in the change_tracking_XXXX tables (there is 1 change_tracking_XXX table for every user table that has CT turned on).
If we are not using auto clean-up, how do we remove outdated records from the change_tracking_XXXX tables? Or is that not something to be concerned about? I guess another way to pose the question is: does the sp_flush_commit_table_on_demand sproc do the same thing that the auto clean-up process does, or am I missing a step?
I''m pretty sure that stored procedure only clears out the syscommittab table. This MS blog has some info and a link to a script for manual cleanup:
Change Tracking Cleanup
Also SP2 for 2014 added a stored proc sp_flush_CT_internal_table_on_demand for manual cleanup. The above link has a script for using that as well
Sue