When Change Tracking Cleanup Falls Behind: A Safe Playbook for Azure SQL
A growing Change Tracking table is a symptom. The useful question is whether cleanup is slow, blocked, or behaving as configured. Change Tracking is a lightweight mechanism for applications that need to determine which rows have changed without building a custom tracking framework. Azure SQL Database supports Change Tracking, including its built-in background cleanup mechanism. Track Data Changes But in long-running or high-change-rate environments, you may eventually encounter a different question: Why does Change Tracking metadata continue to grow? Possible explanations include: The observed growth is still within the configured retention window. Cleanup is progressing, but slower than new changes are arriving. Cleanup isn't making meaningful progress. The workload has changed substantially. A synchronization consumer has fallen behind. A long-running workload requires further investigation. The situation requires Azure support investigation. The important lesson is simple: Don't start by deleting Change Tracking metadata. Start by measuring it. 1. Understand the Change Tracking Contract Change Tracking records committed inserts, updates, and deletes for enabled tables. Applications typically maintain a synchronization version and later ask Azure SQL Database which rows changed after that version. Change Tracking Functions Three concepts are particularly important. Current version SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version; CHANGE_TRACKING_CURRENT_VERSION() returns a version associated with the last committed transaction. An application can preserve this version and subsequently use it when enumerating changes. Change Tracking Functions Minimum valid version For individual tracked tables: SELECT s.name AS schema_name, t.name AS table_name, CHANGE_TRACKING_MIN_VALID_VERSION(t.object_id) AS min_valid_version FROM sys.tables AS t JOIN sys.schemas AS s ON t.schema_id = s.schema_id JOIN sys.change_tracking_tables AS ct ON t.object_id = ct.object_id ORDER BY s.name, t.name; The minimum valid version defines how far back a consumer can safely request changes. If the consumer's saved version falls below the minimum valid version, a subsequent CHANGETABLE result might no longer contain the complete change history the consumer expects. CHANGE_TRACKING_MIN_VALID_VERSION Retention The database's retention setting defines how long Change Tracking information should be retained before becoming eligible for cleanup. That's important because: Retention is not the same thing as a synchronization schedule. A consumer must synchronize frequently enough that its saved version doesn't fall behind the minimum valid version. 2. Start With the Configuration Before investigating cleanup, verify that Change Tracking is configured the way you think it is. SELECT DB_NAME(database_id) AS database_name, is_auto_cleanup_on, retention_period, retention_period_units_desc FROM sys.change_tracking_databases WHERE database_id = DB_ID(); Check: Is Change Tracking enabled? Is automatic cleanup enabled? What is the retention period? Does the retention period match the application's synchronization expectations? Microsoft documents sys.change_tracking_databases as one of the catalog views used to determine databases where Change Tracking is enabled. Manage Change Tracking If cleanup was intentionally disabled, metadata growth has a very different explanation than an environment where automatic cleanup is enabled. 3. Inventory Every Change-Tracked Table Next, understand the scope. SELECT s.name AS schema_name, t.name AS table_name, ct.is_track_columns_updated_on, ct.begin_version, ct.cleanup_version, ct.min_valid_version FROM sys.change_tracking_tables AS ct JOIN sys.tables AS t ON ct.object_id = t.object_id JOIN sys.schemas AS s ON t.schema_id = s.schema_id ORDER BY s.name, t.name; sys.change_tracking_tables returns one row for each Change Tracking-enabled table in the current database and exposes several useful diagnostic columns. sys.change_tracking_tables In particular: begin_version Represents the database version associated with the beginning of Change Tracking for the table. cleanup_version Represents the version up to which cleanup might have removed Change Tracking information. min_valid_version Represents the minimum Change Tracking version that's still valid for synchronization. sys.change_tracking_tables This immediately establishes: How many tables are tracked. Which tables started tracking at different points. Whether cleanup-related metadata differs significantly across tables. Whether your investigation affects one table or the entire database. 4. Capture Current and Minimum Valid Versions Together For a concise health snapshot: SELECT s.name AS schema_name, t.name AS table_name, CHANGE_TRACKING_CURRENT_VERSION() AS current_version, ct.begin_version, ct.cleanup_version, CHANGE_TRACKING_MIN_VALID_VERSION(t.object_id) AS min_valid_version FROM sys.change_tracking_tables AS ct JOIN sys.tables AS t ON ct.object_id = t.object_id JOIN sys.schemas AS s ON t.schema_id = s.schema_id ORDER BY s.name, t.name; This isn't a complete cleanup-health metric by itself. Instead, use it to understand how the Change Tracking version boundaries evolve over time. The key is: One snapshot isn't enough. A single sample can make healthy cleanup appear stuck, and a temporary movement can make an unhealthy trend look normal. 5. Measure Progress Over Time A better investigation takes repeated observations. For example: Sample Current Version Minimum Valid Version Cleanup Version T1 850000 710000 709900 T2 870000 728000 727900 T3 892000 746000 745900 These are synthetic values, but they demonstrate the important question. Ask: Are the boundaries moving forward over time? If yes: Cleanup is demonstrating progress. The next question is whether that progress is sufficient relative to the workload and retention expectations. If no: Extend the observation window. Validate configuration. Check workload conditions. Determine whether the lack of progress is persistent. Do not diagnose cleanup failure from a single observation. 6. Monitor Change Tracking Storage Safely Microsoft documents that Change Tracking creates internal tables, and sys.internal_tables can be used to identify those objects. Manage Change Tracking For investigation, you can inventory Change Tracking internal objects without modifying them: SELECT it.object_id, it.name AS internal_table_name, it.internal_type_desc, OBJECT_SCHEMA_NAME(it.parent_object_id) AS parent_schema_name, OBJECT_NAME(it.parent_object_id) AS parent_table_name FROM sys.internal_tables AS it WHERE it.internal_type_desc LIKE '%CHANGE_TRACKING%' ORDER BY parent_schema_name, parent_table_name; This is a diagnostic inventory. Do not: Delete rows directly from internal tables. Modify internal-table schema. Create unsupported indexes on internal objects. Treat internal Change Tracking tables like customer-owned tables. Microsoft explicitly documents that sys.internal_tables lists the internal objects created when Change Tracking is enabled. Manage Change Tracking 7. Estimate the Storage Footprint If you need to understand whether Change Tracking storage is increasing, combine the documented internal-table catalog metadata with partition statistics for measurement. SELECT it.name AS internal_table_name, OBJECT_SCHEMA_NAME(it.parent_object_id) AS parent_schema_name, OBJECT_NAME(it.parent_object_id) AS parent_table_name, SUM(ps.row_count) AS approximate_rows, CAST ( SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2) ) AS reserved_mb, CAST ( SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2) ) AS used_mb FROM sys.internal_tables AS it JOIN sys.dm_db_partition_stats AS ps ON it.object_id = ps.object_id WHERE it.internal_type_desc LIKE '%CHANGE_TRACKING%' GROUP BY it.name, it.parent_object_id ORDER BY reserved_mb DESC; Use this as a measurement query only. The most useful information isn't usually the absolute number. It's the trend: T1 → 800 MB T2 → 900 MB T3 → 1,050 MB T4 → 1,220 MB versus: T1 → 800 MB T2 → 820 MB T3 → 790 MB T4 → 810 MB The first pattern suggests sustained net growth. The second could simply reflect normal accumulation and cleanup behavior. 8. Validate the Synchronization Consumer Cleanup health is only half of the story. You must also protect the consumer's synchronization contract. Imagine an application stores its last synchronized Change Tracking version: DECLARE @last_sync_version bigint = 700000; DECLARE @table_id int = OBJECT_ID(N'dbo.SampleTable'); DECLARE Min_valid_version bigint = CHANGE_TRACKING_MIN_VALID_VERSION(@table_id); SELECT @last_sync_version AS last_sync_version, Min_valid_version AS min_valid_version, CASE WHEN Min_valid_version IS NULL THEN N'Unable to validate' WHEN @last_sync_version < Min_valid_version THEN N'Reinitialization required' ELSE N'Incremental synchronization valid' END AS sync_status; Microsoft specifically recommends validating a saved synchronization version against CHANGE_TRACKING_MIN_VALID_VERSION(). If last_sync_version is lower than the minimum valid version, subsequent CHANGETABLE results might not be valid for the intended synchronization. CHANGE_TRACKING_MIN_VALID_VERSION That's why simply shortening retention to reduce storage isn't necessarily harmless. You may create a different problem: Consumers can fall outside the valid synchronization window. 9. Use the Safe Change Enumeration Pattern Once the consumer's saved version is known to be valid, it can request subsequent changes. For example: DECLARE @last_sync_version bigint = 700000; SELECT CT.SampleID, CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_CREATION_VERSION, CT.SYS_CHANGE_OPERATION FROM CHANGETABLE ( CHANGES dbo.SampleTable, @last_sync_version ) AS CT; CHANGETABLE(CHANGES...) returns Change Tracking information for changes that occurred since the supplied version. Change Tracking Functions But validate first: DECLARE @last_sync_version bigint = 700000; DECLARE Min_valid_version bigint = CHANGE_TRACKING_MIN_VALID_VERSION ( OBJECT_ID(N'dbo.SampleTable') ); IF Min_valid_version IS NULL BEGIN THROW 50001, 'Unable to validate the Change Tracking version.', 1; END; IF @last_sync_version < Min_valid_version BEGIN THROW 50002, 'Saved synchronization version is no longer valid. Reinitialization is required.', 1; END; SELECT CT.SampleID, CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_CREATION_VERSION, CT.SYS_CHANGE_OPERATION FROM CHANGETABLE ( CHANGES dbo.SampleTable, @last_sync_version ) AS CT; This is much safer than assuming a consumer can request changes indefinitely. 10. Don't Confuse Consumer Lag With Cleanup Failure Suppose: Auto-cleanup is enabled. Cleanup versions are moving forward. Internal-table size fluctuates. A synchronization consumer hasn't run for longer than the retention window. Then the consumer's failure doesn't automatically mean cleanup malfunctioned. Cleanup may have behaved exactly as configured. The real issue may be: The consumer failed to synchronize within the retention contract. This distinction is important because increasing cleanup aggression wouldn't solve that problem. Instead, investigate: Synchronization frequency. Retention configuration. Consumer outages. Failed synchronization jobs. Whether the retention period reasonably accommodates normal recovery time. 11. Slow Cleanup and Blocked Cleanup Aren't the Same Consider three possible behaviors. Pattern A: Progressing normally You observe: Auto-cleanup enabled. Minimum valid versions advancing. Cleanup versions advancing. Storage fluctuating around a range. Consumers healthy. Interpretation: No evidence of a persistent cleanup failure. Pattern B: Progressing but falling behind You observe: Cleanup versions advance. Minimum valid versions advance. Change Tracking storage nonetheless grows over repeated observations. Interpretation: Cleanup appears to be making progress. Net Change Tracking generation may be exceeding cleanup throughput during the observation period. More investigation is warranted. Pattern C: Persistently no observable progress You observe over an appropriate period: Auto-cleanup enabled. Significant stale metadata is expected based on retention. Cleanup version doesn't materially advance. Storage continues growing. The behavior persists. Interpretation: A sustained cleanup backlog becomes a stronger hypothesis. Gather evidence before intervention. Escalation may be appropriate. 12. Check the Workload Before Blaming Cleanup Change Tracking operates alongside your application workload. Consider whether anything changed recently: More tracked tables? Higher DML rates? Larger transaction volumes? More databases using Change Tracking? New bulk workloads? Changes to synchronization frequency? Change Tracking enabled on high-churn tables? Increased contention or long-running operations? Change Tracking adds synchronous work to DML and uses a background cleanup mechanism for stale tracking information. Track Data Changes Therefore, a meaningful diagnosis should correlate metadata growth with: Change rate. Database workload. Cleanup progress. Retention. Consumer behavior. 13. Be Careful With Long-Running Transactions Change Tracking records changes on the basis of committed transactions. Microsoft specifically notes that its ordering is based on transaction commit time, allowing reliable behavior when transactions overlap or run for extended periods. Track Data Changes If you're investigating unusual cleanup timing, long-running workload activity deserves consideration. For currently active database transactions: SELECT s.session_id, r.status, r.command, r.start_time, r.total_elapsed_time, r.cpu_time, r.wait_type, r.wait_time, r.blocking_session_id FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id WHERE s.is_user_process = 1 AND r.database_id = DB_ID() ORDER BY r.total_elapsed_time DESC; Don't automatically conclude that a long-running request blocks Change Tracking cleanup. Instead, treat the workload evidence as another variable to correlate with cleanup behavior. 14. Use a Safe Intervention Hierarchy When metadata growth becomes concerning, resist the temptation to modify system objects. Use this order instead. Step 1: Confirm configuration Check: Change Tracking enabled. Retention. Auto-cleanup. Enabled tables. Step 2: Validate consumers Check: Last synchronization version. Minimum valid version. Synchronization frequency. Failed synchronization jobs. Step 3: Establish a trend Record over time: Current version. Minimum valid version. Cleanup version. Internal storage estimate. Step 4: Check workload changes Correlate: DML volume. Concurrent workload. Long-running operations. Newly tracked tables. Step 5: Reduce avoidable pressure If the workload itself changed, investigate it before assuming an engine defect. Step 6: Use only documented management mechanisms Avoid direct manipulation of Change Tracking internal objects. Step 7: Escalate when evidence shows a persistent backlog Provide: Configuration. Trend measurements. Tracked-table inventory. Version progression. Storage trend. Relevant workload timeline. Consumer status. That creates a much stronger support package than: "syscommittab is large." 15. Validate Recovery Suppose configuration or workload changes are made. Don't validate recovery with one query. Observe an appropriate workload cycle and compare: Metadata size trend. cleanup_version. Minimum valid version. Current version. Consumer synchronization success. Change Tracking-related errors. Workload rate. The desired outcome isn't necessarily: Change Tracking storage becomes tiny. The useful outcome is: Cleanup demonstrates sustainable progress, metadata growth stabilizes relative to workload, and consumers remain inside their valid synchronization window. 16. Prevent the Problem From Returning A healthy Change Tracking deployment needs operational monitoring. Consider tracking: Configuration health Is automatic cleanup enabled? Did retention change? Were new tables added? Cleanup trend Are cleanup versions advancing? Are minimum valid versions advancing? Is metadata storage continuously growing? Consumer health When did each consumer last synchronize? How close is its saved version to the minimum valid version? Are synchronization jobs failing? Workload health Has the write rate changed? Have new bulk workloads appeared? Has database scale changed substantially? The most useful alerts often focus on trend and synchronization safety, not simply: "syscommittab exceeded X GB." Absolute size tells you very little without workload and cleanup context. 17. A Practical Diagnostic Decision Table Observation Interpretation Next step Auto-cleanup disabled Configuration explains lack of automatic cleanup Confirm whether intentional Consumer version below minimum valid version Consumer fell outside valid window Reinitialize according to application design Cleanup versions advancing Cleanup demonstrating progress Continue trend analysis Versions advancing but storage growing Creation may exceed cleanup during observation Correlate workload and change rate No version movement in one snapshot Inconclusive Collect repeated samples Persistent lack of progress + sustained growth Cleanup backlog becomes plausible Gather escalation evidence Storage fluctuates but remains broadly stable Could represent normal operation Continue monitoring Consumer repeatedly approaches minimum valid version Retention/sync schedule may be misaligned Review synchronization design Final Takeaway When Change Tracking metadata grows, don't start with: "How do I delete syscommittab?" Start with: "Is cleanup progressing, is the workload outpacing it, or is the system behaving exactly as configured?" Azure SQL Database provides documented interfaces that let you answer much of that safely: sys.change_tracking_databases sys.change_tracking_tables CHANGE_TRACKING_CURRENT_VERSION() CHANGE_TRACKING_MIN_VALID_VERSION() CHANGETABLE sys.internal_tables for inventory and diagnostic observation Microsoft documents Change Tracking as having a built-in automatic cleanup mechanism and exposes catalog views and functions for managing and consuming its metadata. Track Data Changes Manage Change Tracking The most reliable investigation follows a simple sequence: Confirm configuration. Inventory tracked tables. Validate synchronization consumers. Measure version progression over time. Measure storage as a trend. Correlate growth with workload. Avoid direct system-object manipulation. Escalate with evidence when progress persistently stalls. Most importantly: Don't modify Change Tracking internal tables directly. Those objects are implementation details owned by the database engine. Observe them when documented metadata makes that appropriate, but manage Change Tracking through supported, documented interfaces. Because with Change Tracking cleanup: One snapshot tells you the size. A trend tells you the story. Further reading Track Data Changes About Change Tracking Manage Change Tracking sys.change_tracking_tables CHANGE_TRACKING_MIN_VALID_VERSION Change Tracking Functions
