Change Tracking Cleanup–Part 1


Part 2 of the series is available here.

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications which was introduced in SQL Server 2008. We recently had a number of customers ask us about how Change Tracking Cleanup works and how they can troubleshoot further if the cleanup is not working as expected. In the first part of this blog post, I will explain how Change Tracking cleanup works and what “information” is cleaned up by the automatic cleanup task. I will also touch upon what enhancements were shipped in SQL Server 2014 and above to help cleanup more efficiently.

Change Tracking cleanup is invoked automatically 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)  

Automatic Cleanup

Each table that is enabled for Change Tracking has an internal table (a.k.a. side table with the naming convention: change_tracking_<#>) which is used by Change Tracking functions to determine the change version and the rows that have changed since a particular version. Every time the automatic cleanup thread wakes up, it scans all the user databases on the SQL Server instance to identify the change tracking enabled databases. Based on the retention period setting of the database, each side table is purged of its expired records. The automatic cleanup removes rows from the on-disk tables based on the retention period defined for the database.

Change tracking information is stored for all tables (enabled for Change Tracking) in a database in an in-memory rowstore (syscommittable). This in-memory rowstore is flushed every checkpoint to the on-disk table (syscommittab). Rows from the syscommittab internal table are removed during every checkpoint.

Manual Cleanup

In SQL Server 2014 Service Pack 2 and above, we provided a new Stored Procedure, sp_flush_CT_internal_table_on_demand, to assist with Change Tracking cleanup. KB3173157 has more details. This stored procedure accepts a table name as parameter and will attempt to cleanup records from the corresponding change tracking internal table.  During the course of the deletion, it will print some verbose in the output window about the progress of deletion.

In case you want to automate the cleanup for all tables, you can use a while loop to execute this stored procedure against all the tables or tables that receive a high number of changes to prevent automatic cleanup from lagging in cleaning up records from the Change Tracking internal tables. A sample manual cleanup T-SQL script is available on the tigertoolbox GitHub repo: ChangeTrackingCleanup.sql (see screenshot below).

image[3]

– Amit Banerjee (@banerjeeamit)

Sr. Program Manager

Comments (3)

  1. Chris says:

    Hi Amit,
    Thanks for the detailed post. I’m currently in the process of testing sp_flush_CT_internal_table_on_demand in our dev environment. Our retention period is set to 15 days. I can see the manual cleanup process do its work on its first run. However, after it completes, if I decrease the retention window and run sp_flush_CT_internal_table_on_demand again, it doesn’t delete anything. Why is this?

    1. The stored procedure deletes expired records from the on-disk tables. So the rows in the on-disk table have to be lower than the invalid cleanup version. The invalid cleanup version is the change tracking version which marks the point till which the auto cleanup task will perform the cleanup for the side tables.

      1. Chris says:

        Thanks. If auto-cleanup is turned off, will the invalid cleanup version still get modified as time goes on? Or will the invalid cleanup version only increment with auto-cleanup on?

        We’ve noticed some locking/blocking going on related to the auto cleanup feature in our production environment, so we’re looking at ways to schedule the CT clean-up to only run during off hours. I thought we might be able to accomplish this by scheduling sp_flush_CT_internal_table_on_demand to run during off hours, and completely turn off the auto-cleanup feature. However, I can’t seem to get this to work as I want. Any help/guidance is appreciated! Thanks again.

Skip to main content