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).

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

  • Anonymous
    January 23, 2017
    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?
    • Anonymous
      January 23, 2017
      The comment has been removed
      • Anonymous
        January 25, 2017
        The comment has been removed
        • Anonymous
          December 27, 2017
          Is there an answer to that? Thank you.
  • Anonymous
    May 11, 2017
    What about SQL 2012 servers
    • Anonymous
      June 15, 2017
      We are evaluating this for SQL Server 2012 for a future update.
  • Anonymous
    November 03, 2017
    The comment has been removed