Part 2: Safely Cleaning Orphaned Records in Change Tracking Side Tables
Applies to: Azure SQL Database (Change Tracking enabled) Recap (Part 1) In Part 1, we covered how to detect “orphaned” records in Change Tracking (CT) side tables — rows whose sys_change_xdes_id no longer has a matching transaction entry in the commit table (sys.syscommittab). This situation often leads to unexpected CT growth and “stuck cleanup” symptoms because the mapping data required for normal cleanup is missing. Part 1 link: Identifying Orphaned Records in Change Tracking Side Tables (Read‑Only Health Check) Why Part 2 is needed A common “root pattern” we see in the field is: Side-table cleanup attempts to delete expired metadata Some side-table deletions fail (locks/timeouts/errors) Commit-table cleanup proceeds anyway (or a custom workflow deletes from commit table without validating side-table deletes) Remaining side-table rows now reference xdes_id values that no longer exist in sys.syscommittab → orphans Microsoft Learn also emphasizes that syscommittab cleanup depends on side-table cleanup — commit-table cleanup should happen only after side tables are cleaned. This Part 2 script focuses on removing orphaned rows from side tables (and does not touch sys.syscommittab), so cleanup logic can stabilize again. Important prerequisites & constraints (read this first) 1) Internal table access on Azure SQL Database In Azure SQL Database, customers may not be able to access certain internal CT artifacts directly (even when attempting DAC-style workflows). In the related case discussions, internal testing noted that self‑service cleanup against internal tables can be infeasible. 2) CHECKPOINT note (why it’s in the script) sys.dm_tran_commit_table exposes commit-table data and is backed by sys.syscommittab. Microsoft Learn notes that read-only users may not see live changes until a CHECKPOINT occurs. That’s why your script includes the optional CHECKPOINT comment before reading commit-table state. 3) Supported guidance vs. custom remediation Microsoft Learn provides official troubleshooting/mitigation guidance for CT cleanup issues (including checking dbo.MSChange_tracking_history, assessing stale rows, and using sp_flush_commit_table_on_demand for commit-table cleanup). Your script is a targeted remediation pattern for a specific failure mode (orphaned side-table rows). Use it carefully, test first, and follow organizational approval processes. What this script does (high-level) T-SQL script is essentially Part 1 detection + optional targeted delete generation: Compute the “safe cleanup point” from CT retention (wall-clock → CSN) using sp_changetracking_time_to_csn — the same concept used in Part 1. Enumerate CT side tables via sys.internal_tables where internal_type = 209 (CT side tables). For each side table, identify candidate orphaned transaction IDs (sys_change_xdes_id) that: are older than a computed boundary (@minXdesId derived from sys.dm_tran_commit_table), and have no matching xdes_id in sys.syscommittab at/before the cleanup point Print orphan counts per side table using RAISERROR … WITH NOWAIT (operator-friendly, streaming output). Safety cross-check: abort if any “orphan” unexpectedly exists in sys.syscommittab (defensive sanity gate) Generate a DELETE statement for the current side table (execution is commented out) The script (Part 2) — “detect + generate delete” Below is T-SQL script. I kept the delete step disabled by default, so it remains safe to share. (You can enable execution only after approvals/testing.) -- use <[DBName]> -- switch to the right database -- run checkpoint first to ensure all in-memory commit table data is persisted to disk (syscommittab) -- checkpoint SET NOCOUNT ON -- find the invalid clean version based on configured retention DECLARE time DATETIME, @csn BIGINT = 0, @minCleanupPoint BIGINT = 0 DECLARE @retention_period INT, @retention_period_units NVARCHAR(10) SELECT @retention_period = retention_period, @retention_period_units = retention_period_units FROM sys.change_tracking_databases where database_id = DB_ID() SELECT time = CASE WHEN @retention_period_units = 1 then DATEADD(minute, (-1 * @retention_period), GETUTCDATE()) WHEN @retention_period_units = 2 then DATEADD(hour, (-1 * @retention_period), GETUTCDATE()) ELSE DATEADD(day, (-1 * @retention_period), GETUTCDATE()) END EXEC sp_changetracking_time_to_csn time = time, @csn = @csn OUTPUT SELECT @minCleanupPoint = @csn SELECT @minCleanupPoint as minCsn -- 688118 -- iterate over all the change tracking side tables DECLARE @sideTable SYSNAME; DECLARE ct_cursor CURSOR FAST_FORWARD FOR SELECT name FROM sys.internal_tables WHERE internal_type = 209; -- internal_type = 209 is for change tracking side tables OPEN ct_cursor; FETCH NEXT FROM ct_cursor INTO @sideTable; WHILE @@FETCH_STATUS = 0 BEGIN -- find the minimum expired xdes id declare @minXdesId BIGINT SELECT @minXdesId = min(xdes_id) FROM sys.dm_tran_commit_table where commit_ts <= @minCleanupPoint -- SELECT @minXdesId as minXdes -- create temp table for storing orphaned xdes id DROP TABLE IF EXISTS #OrphanedXdes; CREATE TABLE #OrphanedXdes ( sys_change_xdes_id BIGINT NOT NULL ); DECLARE @sql NVARCHAR(MAX); SET @sql = N' INSERT INTO #OrphanedXdes(sys_change_xdes_id) SELECT ct.sys_change_xdes_id FROM sys.' + QUOTENAME(@sideTable) + N' AS ct WHERE ct.sys_change_xdes_id < @minXdesId AND NOT EXISTS ( SELECT 1 FROM sys.syscommittab AS s WHERE s.xdes_id = ct.sys_change_xdes_id AND s.commit_ts <= @minCleanupPoint );'; EXEC sys.sp_executesql @sql, N'@minXdesId BIGINT, @minCleanupPoint BIGINT', @minXdesId = @minXdesId, @minCleanupPoint = @minCleanupPoint; DECLARE @orphanedIdsCount BIGINT; SET @sql = N' SELECT @cnt = COUNT_BIG(sys_change_xdes_id) FROM #OrphanedXdes; '; EXEC sys.sp_executesql @sql, N'@cnt BIGINT OUTPUT', @cnt = @orphanedIdsCount OUTPUT; -- Raise error if any orphaned xdes exists IF (@orphanedIdsCount > 0) BEGIN DECLARE @msg NVARCHAR(4000) = @sideTable + N' : ' + CONVERT(NVARCHAR(30), @orphanedIdsCount); RAISERROR (@msg, 16, 1) WITH NOWAIT; DECLARE @newLine NVARCHAR(10) = CHAR(13) + CHAR(10) PRINT (@newLine) END -- Cross-check that no xdes should exist in syscommittab -- !!!IMPORTANT!!! RAISE an error and stop the cleanup if it does SET @sql = N' DECLARE @nonMatchingXdesCount BIGINT; SELECT @nonMatchingXdesCount = COUNT_BIG(*) FROM #OrphanedXdes AS ct WHERE EXISTS ( SELECT 1 FROM sys.syscommittab AS s WHERE s.xdes_id = ct.sys_change_xdes_id ); -- SELECT @nonMatchingXdesCount as nonMatchingXdesCount IF (COALESCE(@nonMatchingXdesCount, 0) > 0) BEGIN TRY DECLARE @msg NVARCHAR(1024); SET @msg = N''Cleanup aborted: orphan cross-check failed for side table [' + @sideTable + N'].''; RAISERROR(@msg, 16, 1) WITH NOWAIT; RETURN; END TRY BEGIN CATCH THROW; END CATCH '; EXEC sys.sp_executesql @sql; IF (@orphanedIdsCount > 0) BEGIN -- Prepare the query to delete the orphaned rows from the side table SET @sql = N'DELETE ct FROM sys.' + @sideTable + N' ct WHERE EXISTS (SELECT 1 FROM #OrphanedXdes AS o WHERE o.sys_change_xdes_id = ct.sys_change_xdes_id);'; SELECT @sql -- validate the delete query is correctly generated -- Sample delete statement: DELETE ct FROM sys.change_tracking_1221579390 ct WHERE EXISTS (SELECT 1 FROM #OrphanedXdes AS o WHERE o.sys_change_xdes_id = ct.sys_change_xdes_id); -- NOTE: Uncomment the below query to execute the delete statement and remove the orphaned records -- EXEC sys.sp_executesql @sql; END DROP TABLE IF EXISTS #OrphanedXdes; FETCH NEXT FROM ct_cursor INTO @sideTable; END CLOSE ct_cursor; DEALLOCATE ct_cursor; SET NOCOUNT OFF Why the “cross-check abort” is a good idea Notice the safety gate: You first define orphans as those not existing in sys.syscommittab (for the cleanup horizon). Then you re-check: “If any of these appear in syscommittab, abort.” This prevents an accidental delete if: the cleanup horizon math is wrong, the environment has unexpected visibility differences, or the temp-table contents are not what you expect. That defensive posture aligns well with the general principle documented in Microsoft Learn: commit-table cleanup should only occur after side-table cleanup, and troubleshooting should be data-driven and cautious. How to interpret the output If you see no RAISERROR lines, the script did not find orphaned rows under the defined criteria. If you see: change_tracking_<id> : <count> that indicates <count> orphaned transaction references in that CT side table. This is the same style used in Part 1 for long-running, streaming progress. Next steps (recommended order) Confirm CT configuration (retention + AUTO_CLEANUP status) using official guidance. Run Part 1 / Part 2 detection to quantify scope (which side tables, how many). If you need to remediate: Prefer supported mitigations where possible (for example, disable/enable CT on a table to purge tracking metadata for that table is listed as a “quickest remedy” in Microsoft Learn for certain cleanup lock conflict scenarios). If table-level disable/enable isn’t acceptable, use an approval-driven approach for targeted cleanup. Closing Orphaned CT side-table records are one of those “silent growth” conditions that can be easy to miss until storage or CHANGETABLE performance becomes painful. Part 1 helps you spot the issue early; Part 2 helps you prepare a safe, targeted cleanup workflow — with explicit safety gates and a delete step that remains disabled by default. References Identifying Orphaned Records in Change Tracking Side Tables (Read‑Only Health Check) Troubleshoot change tracking auto cleanup issues sys.dm_tran_commit_table (Change Tracking)
