First published on MSDN on Jan 17, 2017
In the
https://blogs.msdn.microsoft.com/sql_server_team/change-tracking-cleanup-part-1/
of my Change Tracking Cleanup post, I ...
Updated Mar 23, 2019
Version 2.0
Blog Post
We have a database with change tracking enabled on two very active tables. Our database size keeps increasing and I have determined that the change tracking side tables are not getting flushed frequently enough. I started running sp_flush_CT_internal_table_on_demand against the two tables and, even with that running constantly, the side tables still grow. Our database is increasing by 500 MB every 40 minutes. The change tracking side tables currently have 10.5 billion and 2.5 billion rows currently. I’ve had sp_flush_CT_internal_table_on_demand running against both tables for 3 hours. In that time, it has deleted 16.5 million rows from one table and 900K rows from the other. Even with those procs running constantly, the change tracking side tables are still growing by about 250K and 4.5K rows per minute.
I am running SQL 2016 SP2 CU8. We have change tracking enabled with track columns updated enabled as well. Do you have any suggestions?