Manual Cleanup of Change Tracking in SQL Server 2014

Question

Manual Cleanup of Change Tracking in SQL Server 2014

chris.o.smith

SSCommitted

Points: 1674
More actions
January 17, 2017 at 2:29 pm

#320595

My company uses change tracking for our data warehouse incremental loads, as described here by Tim Mitchell. We have a retention period of 15 days, and auto clean-up set to TRUE. We've been seeing some long-running clean-up tasks during our "peak" hours, so we'd like to schedule the clean-up tasks to only run during "off" hours. Unfortunately, I don't see a way to limit the auto clean-up feature to a particular time of day. 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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

chris.o.smith - Tuesday, January 17, 2017 2:29 PM
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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

This is a part of the reason why I grow my own auditing. It can be scheduled, controlled, and tuned for the least impact and resource usage/commitment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chris.o.smith SSCommitted Points: 1674 More actions · Answer 3

Sue_H - Tuesday, January 17, 2017 4:36 PM
chris.o.smith - Tuesday, January 17, 2017 2:29 PM
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

Thanks for the blog link - that we exactly what I was looking for.