Manually delete change tracking records in SQL Server

Question

I can't find any means to manually perform a cleanup on the change tracking table after a client has been updated. This seems to be a major limitation of change tracking in SQL Server, unless i'm missing something.

Perhaps I'm overlooking something, but what i'm trying to accomplish is after a client is successfully updated from SQL Server to removed those change records in the change table. They are not needed at that point.

The only configuration i'm aware of is the retention period of 2 days... etc. Because different clients/people will sync at VERY different intervals it seems like my only choice is to set a very large retention period: e.g. 365 days. But doing so will result in bloat without the ability to purge once all clients are updated.

As such seems like the only work around is to manually create triggers and maintain my own delete tables, update tables, etc.

Has anyone found a better way to manage this rather than simply not using the change tracking feature as implemented?

Answer 1

Set your retention period to a length of time that is reasonable and functional. If a client tries to sync using an expired version (less than min_valid_version in sys.change_tracking_tables), it must re-sync the entire table. Hopefully you don't have too many clients waiting months to sync up. If so... at least they're not doing it very often.

Answer 2

Don't have a perfect solution, but for space intensive servers, I've dropped the table and recreated from a copy and then re-enabled change tracking. This deleted all the change tracking records for that table. I did this as part of regular maintenance.

I've used sys.sp_cdc_disable_db to disable change tracking during maint to ease this process.

Prior to this method, I had my own tracking system implemented. But with replication, remote, and failover server issues, it became very complicated to track all those changes. It is a pain especially if one has implement automatic restore features. The overhead was not worth it.

Hope this helps.