Safely Cleaning Orphaned Records in Change Tracking Side Tables

Detecting and Removing Orphaned Change Tracking Records

This article continues the discussion from Part 1, focusing on safely cleaning orphaned records in Change Tracking (CT) side tables in Azure SQL Database.

Why Orphaned Records Occur

A common scenario leading to orphaned records:

• Side-table cleanup attempts to delete expired metadata
• Some deletions fail due to locks, timeouts, or errors
• Commit-table cleanup proceeds anyway
• Remaining side-table rows reference transaction IDs (xdes_id) that no longer exist in sys.syscommittab

This creates orphaned records that can cause unexpected CT growth and cleanup failures.

Script Overview

The provided T-SQL script performs detection and generates delete statements:

1. Computes cleanup boundary using CT retention settings via sp_changetracking_time_to_csn
2. Enumerates CT side tables using sys.internal_tables where internal_type = 209
3. Identifies orphaned transaction IDs that are:
   • Older than the computed boundary
   • Missing from sys.syscommittab at the cleanup point
4. Prints orphan counts per side table using RAISERROR with NOWAIT for streaming output
5. Includes safety cross-check that aborts if any "orphan" unexpectedly exists in sys.syscommittab
6. Generates DELETE statements (disabled by default for safety)

Important Prerequisites

• Azure SQL Database limitations: Direct access to internal CT artifacts may be restricted
• CHECKPOINT requirement: Ensures in-memory commit table data is persisted to disk before reading
• Supported vs. custom remediation: Official Microsoft Learn guidance provides troubleshooting steps, while this script addresses a specific failure mode

Safety Features

The script includes critical safety mechanisms:

• Cross-check abort: Re-validates that identified orphans don't exist in sys.syscommittab
• Delete disabled by default: Requires explicit uncommenting after approval
• Streaming output: Uses RAISERROR ... WITH NOWAIT for real-time progress monitoring
• Defensive programming: Includes error handling and validation steps

How to Use

1. Run Part 1 first to detect orphaned records and assess scope
2. Confirm CT configuration using official guidance
3. Interpret output: No RAISERROR lines means no orphans found; otherwise, see counts per side table
4. Consider remediation options: Prefer supported mitigations (like disabling/enabling CT on tables) before using targeted cleanup
5. If proceeding with cleanup: Test thoroughly, follow organizational approval processes, and enable delete statements only after validation

Key Takeaway

Orphaned CT side-table records can cause silent storage growth and performance issues. This two-part approach helps identify problems early (Part 1) and provides a safe, targeted cleanup workflow (Part 2) with explicit safety gates and disabled delete operations by default.