Advanced Data Solutions : Change Tracking Cleanup

Part 1 of the series discusses how automatic and manual cleanups happen in SQL Server.

Part 2 of the series covers how the cleanup actually works with the help of some metadata from an actual Change Tracking implementation.

Change Tracking cleanup summary

Change Tracking automatic cleanup is invoked every 30 minutes. The default retention period is 2 days. An example of setting the automatic cleanup for Change Tracking information is shown below:

ALTER DATABASE <DBNAME> SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

The stored procedure sp_flush_CT_internal_table_on_demand assists with Change Tracking manual cleanup and is available in SQL Server 2014 Service Pack 2 and above. This stored procedure accepts a table name as parameter and will attempt to cleanup records from the corresponding change tracking internal table. During the deletion, it will print some verbose in the output window about the progress of deletion. To learn more about this stored procedure, go here: KB3173157 - Adds a stored procedure for the manual cleanup of the change tracking side table in SQL Server (microsoft.com).

sp_flush_CT_internal_table_on_demand [ @TableToClean= ] 'TableName'

New changes to manual cleanup stored procedure

The behavior of the sp_flush_CT_internal_table_on_demand stored procedure has been modified to do the following:

1.) If tablename parameter is passed, it will do the cleanup for corresponding side-table alone using the current invalid version as the watermark. This is the existing behavior, and this option should be used to clear any backlogs left by the cleanup thread.

2.) If tablename parameter is not passed, then it does the following

a.) Determine the invalid version based on the retention period. Persist this value in sysobjvalues table.

b.) Use the invalid version from step 2a to do the cleanup on all side tables. If there are tables for which cleanup failed, add that to a separate list (let us call this as error list) and proceed with other tables. After completing all tables, check if there are any tables in the error list and retry these tables.

c.) If the error list is not empty even after a retry, return, If the error list is empty, proceed to step d.

d.) Update Hardened cleanup version and persist the value in sysobjvalues.

e.) Cleanup the syscommittab table with the hardened version from step 2d as the watermark.

Following screenshots show the steps from 2a to 2e. Be aware that DAC connection is needed to query any internal tables or sysobjvalues.

Created 2 tables – Test1 and Test2 – and enabled them for change tracking. Retention Period is 2 mins and auto cleanup is off. Inserted 10k records into these 2 tables.

Waited for 2 mins so that these records will be considered as expired.
Auto cleanup is turned off and even if it is turned on, we won’t have the first iteration of auto cleanup in the next 30 mins. Following is the proof that invalid and hardened versions are not yet set.

Called the manual clean proc without any tablename. This will do the end-to-end work for our expired entries cleanup (updating invalid version, cleaning up sidetable, retry incase of error, updating hardened version and finally syscommittab cleanup). All these will be done without even using DAC connection.