As I’ve previously mentioned, the automatic cleanup process of the syscommittab table limits you to being able delete 14.4 million rows a day. This is a problem in a highly transactional system with volatile tables.

Where the automated cleanup process let’s you down there is an undocumented stored procedure which will allow you to perform a manual cleanup of syscommittab in order to prevent it from growing out of control.

The procedure sys.sp_flush_commit_table_on_demand accepts a BIGINT for the number of rows that you want to delete from syscommittab. 

When the procedure executes it will delete as many rows as you pass in, doing so in batches of 10,000. It will not delete any rows that are newer than the minimum cleanup date, so you do not have to worry about deleting too much data and running into a problem with no longer being able to use your Change Tracking tables.

While the sp_flush_commit_table_on_demand has a deadlock priority of low I would recommend temporarily disabling the automatic cleanup process when running it, just to prevent possible locking or blocking issues with new rows trying to be inserted. It also makes the most sense to run this during a quieter time on your SQL Instance, just to reduce the impact further. 

You can temporarily disable Change Tracking cleanup by running


ALTER DATABASE CCTest SET CHANGE_TRACKING (AUTO_CLEANUP = OFF);

When you are done with the manual cleanup you can re-enable by running


ALTER DATABASE CCTest SET CHANGE_TRACKING (AUTO_CLEANUP = ON, CHANGE_RETENTION = 24 HOURS);


Note, when re-enabling Change Tracking I’ve included the CHANGE_RETENTION information. If you do not include this then the default of 72 hours will be used. This may be too high or too low, depending on your needs, so be careful.

In summation, for a busy system watch the size of the syscommittab table and if necessary run sys.sp_flush_commit_table_on_demand if the automated cleanup process cannot keep up.