Identifying Orphaned Records in Change Tracking Side Tables (Read‑Only Health Check)

When Change Tracking (CT) is enabled, SQL Server (and Azure SQL Database) stores lightweight change metadata so applications can query “what changed since version X?”. Under the hood, CT maintains:

• A side table per tracked user table, storing row-level change metadata (including the transaction identifier).
• A commit table that stores the committed transactions that touched any change-tracked table. The DMV sys.dm_tran_commit_table exposes the union of the in-memory and on-disk parts of this commit table.

In a healthy system, CT cleanup removes expired rows from side tables and then removes the corresponding older commit-table rows based on the configured retention. Internal cleanup logic uses a “safe cleanup point” obtained from the configured retention and maps that wall-clock time to a CT version using sys.sp_changetracking_time_to_csn. 

However, in some scenarios (commonly involving custom/manual cleanup workflows that don’t fully validate side-table deletion outcomes), commit-table entries may be removed while some side-table rows remain. That leaves orphaned records: side-table rows referencing transaction IDs that no longer exist in the commit table. This condition can cause persistent growth and “stuck” CT cleanup symptoms because the mapping data needed for correct cleanup is missing.

This post walks through a read-only T‑SQL script that detects and counts orphaned records across all CT side tables in a database.

What are “orphaned” CT records?

Each row-level change captured by CT is associated with a transaction identifier (commonly referred to as xdes_id). Side tables store this identifier in sys_change_xdes_id. The commit table tracks committed transactions and includes columns such as commit_ts and xdes_id.

An orphaned CT record, in this context, is:

A row in a CT side table whose sys_change_xdes_id no longer has a corresponding row in the commit table (sys.syscommittab) at/before the cleanup point.

Why does that matter? CT cleanup for side tables typically deletes rows that match commit-table entries up to the cleanup point. If commit-table entries were removed prematurely, the side-table rows can become “unmatchable”, meaning they will never be deleted by normal CT cleanup.

Prerequisites & notes

1. Permissions / access: The script enumerates internal CT side tables (sys.internal_tables where internal_type = 209) and checks sys.syscommittab. Side-table internal type 209 is used for CT side tables. 
2. Read-only intent: This script does not delete data. It only counts and reports potential orphans.
3. Performance: On databases with many CT-enabled tables or large CT metadata, expect this to take time. It loops through each side table and performs a lookup-based existence test.
4. Output style: It uses RAISERROR ... WITH NOWAIT to print findings as it runs (useful for long executions).

The script (as provided)

Tip: Replace <[DBName]> with your database name or simply run it in the correct database context.

-- use <[DBName]> -- switch to the right database

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

	DROP TABLE IF EXISTS #OrphanedXdes;

    FETCH NEXT FROM ct_cursor INTO @sideTable;
END

CLOSE ct_cursor;
DEALLOCATE ct_cursor;

SET NOCOUNT OFF

Step-by-step: What the script is doing (and why)

1) Read CT retention settings for the database

The script reads the CT retention period from sys.change_tracking_databases:

• retention_period (numeric)
• retention_period_units where:
  • 1 = Minutes
  • 2 = Hours
  • 3 = Days

Those values define how long CT metadata is meant to be retained before cleanup is allowed to remove it.

2) Compute a “cutoff time” based on retention

It subtracts the retention window from GETUTCDATE() to compute time, i.e. the oldest point in time that should still be retained.

3) Convert the cutoff time into a CT cleanup version (@minCleanupPoint)

Cleanup in CT is driven by a version watermark (often represented as a CSN). The script calls:

• sp_changetracking_time_to_csn time = ..., @csn = ... OUTPUT

…and assigns that CSN to @minCleanupPoint.

Conceptually: “Give me the CT version that corresponds to the oldest time that should be retained. Anything older than that is eligible to be cleaned.”

This matches how cleanup designs determine the “safe cleanup point” from retention.

4) Enumerate CT side tables (internal type 209)

CT side tables are stored as internal tables, and the script uses:

SELECT name FROM sys.internal_tables WHERE internal_type = 209;

Internal type 209 corresponds to CT side tables.

Each side table name is like change_tracking_<id> and is associated with a tracked user table.

5) Determine the minimum “expired” transaction id (@minXdesId)

The commit table contains one row per committed transaction that affected tracked tables, and it includes xdes_id and commit_ts. The DMV sys.dm_tran_commit_table provides access to the commit-table contents.

The script computes:

SELECT @minXdesId = min(xdes_id)
FROM sys.dm_tran_commit_table
WHERE commit_ts <= @minCleanupPoint;

Think of @minXdesId as:

“Among commit-table entries that are at or before the cleanup point, what’s the smallest transaction id still represented?”

This is used as an optimization boundary in the next step.

6) For each side table, capture orphaned sys_change_xdes_id

Inside dynamic SQL (because the side table name changes per iteration), it inserts into a temp table any sys_change_xdes_id values that meet two criteria:

1. Older than the minimum expired transaction id

ct.sys_change_xdes_id < @minXdesId

This narrows the scan to candidates that are definitely older than the current “oldest still-known” commit-table transaction.

No matching commit-table row exists up to the cleanup point

NOT EXISTS (
  SELECT 1
  FROM sys.syscommittab AS s
  WHERE s.xdes_id = ct.sys_change_xdes_id
    AND s.commit_ts <= @minCleanupPoint
)

sys.syscommittab holds the persistent (on-disk) commit table. The commit table includes commit_ts and xdes_id, with indexes including a unique nonclustered index on (xdes_id), making this existence test efficient when accessible.

 

If a side-table row references a transaction id that no longer exists in the commit table (for the relevant cleanup horizon), it is treated as orphaned.

This pattern aligns with the documented cleanup approach where side tables are cleaned based on sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab WHERE commit_ts <= @csn)—so if the xdes_id is missing in sys.syscommittab, the side-table row can’t be matched for cleanup.

7) Count and report per side table

For each side table, it counts the number of rows in #OrphanedXdes and, if greater than 0, outputs:

change_tracking_XXXXXXXX : <count>

using RAISERROR ... WITH NOWAIT so you can see progress in real time.

Interpreting results

• No output (or no RAISERROR lines) generally means no orphaned rows detected under the script’s criteria.
• Output like:

change_tracking_2140287130 : 12345

means that side table contains 12,345 orphaned transaction references (i.e., sys_change_xdes_id values not found in the commit table up to the cleanup point).

If you see orphans in many side tables, it often indicates a systemic issue (for example, a manual cleanup job that deletes from commit table even when side-table deletes fail). This exact failure mode—commit table deletion proceeding even when side table deletion errors occur—has been described as a root cause for orphaned CT side-table rows in real investigations.

Why orphans can happen (common pattern)

A recurring pattern is:

1. Cleanup logic identifies “eligible” side-table rows based on commit-table commit_ts / cleanup version.
2. Side-table delete encounters contention/locking and does not remove all rows.
3. Cleanup proceeds to delete commit-table rows anyway.
4. The remaining side-table rows now reference xdes_id values that no longer exist in the commit table → orphaned.

This sequence is explicitly called out in internal design discussion and field observations: side tables should be cleaned first, and commit-table deletion should only happen after successful side-table cleanup.

Operational tips & safe next steps

1) Run this as a periodic health check
If you operate at scale (many databases / many tracked tables), running this periodically helps you detect drift early—before CT metadata growth becomes painful.

2) If you find orphans, treat it as a symptom—not the root cause
Before remediation, validate:

• Is auto-cleanup enabled (is_auto_cleanup_on)?
• Are you running any custom/manual cleanup routines that touch CT internals?
  These values are visible via sys.change_tracking_databases.

3) Align manual cleanup behavior with documented cleanup sequencing
The general cleanup principle is that side-table deletion should be coordinated with (and precede) commit-table cleanup. The cleanup design description references obtaining the cleanup point with sys.sp_changetracking_time_to_csn and then deleting side-table rows in batches based on commit-table membership

Optional enhancements (quality-of-life)

If you want to make this even more operator-friendly (still read-only), consider:

• Return a result set instead of (or in addition to) RAISERROR:
  • SideTableName, OrphanedCount, MinOrphanXdes, MaxOrphanXdes
• Aggregate totals across all side tables at the end.
• Add timing per table to identify hotspots.

These are purely usability improvements; the core detection logic stays the same.