Change Tracking Cleanup Issues in Azure SQL Database: Orphaned Rows and Auto-Cleanup Behavior

Azure SQL Database's Change Tracking (CT) feature, while valuable for tracking data modifications, can encounter significant cleanup challenges that manifest as unexpected growth in side tables and orphaned records. This issue becomes particularly pronounced when environments switch between auto-cleanup and custom manual cleanup approaches, creating operational headaches for database administrators.

The core problem stems from how Change Tracking manages its internal commit tracking and cleanup processes. When auto-cleanup is disabled or when manual cleanup procedures are implemented, CT side tables can accumulate rows that reference commit transactions no longer present in the system's commit tracking tables. These orphaned rows not only consume storage but can also impact query performance and complicate maintenance operations.

A key insight from troubleshooting efforts reveals that snapshot isolation can significantly interfere with cleanup operations. Long-running snapshot transactions prevent the cleanup process from advancing its "safe cleanup point," effectively blocking the removal of expired entries from internal commit tracking tables. This creates a scenario where cleanup appears to be functioning but is actually stalled due to active snapshot transactions holding locks on commit information.

To diagnose these issues, several practical approaches have proven effective. First, validating the Change Tracking configuration through system views like sys.change_tracking_databases helps establish whether auto-cleanup is enabled and what retention period is configured. A lightweight query against sys.dm_tran_commit_table can provide a quick "backlog signal" by examining the oldest commit_time - if this value is close to the retention horizon, auto-cleanup is likely keeping pace.

For identifying orphaned rows specifically, a read-only diagnostic script iterates through all CT side tables (identified by internal_type = 209 in sys.internal_tables) and compares their sys_change_xdes_id values against the commit tracking table. This script calculates a cleanup point based on configured retention, then identifies rows whose transaction IDs no longer have matching entries in the commit tracking system. The script raises errors for any tables containing orphaned rows, providing a clear scope of the problem.

When auto-cleanup is unexpectedly disabled, the recommended approach is to re-enable it and monitor cleanup activity through the CT history tables. This allows the system to resume its normal cleanup cadence without manual intervention. However, if orphaned rows persist or if auto-cleanup cannot be re-enabled, a safer mitigation involves disabling and re-enabling Change Tracking on affected tables. While this approach results in loss of existing change data for those tables, it provides a clean slate without requiring elevated permissions for manual cleanup operations.

The investigation also highlighted the critical importance of auditing and Extended Events for troubleshooting. Without proper auditing, determining who disabled auto-cleanup or dropped related objects becomes nearly impossible. Extended Events can capture DDL operations and application context information that helps identify the source of unexpected configuration changes.

Performance considerations favor maintaining auto-cleanup when possible. Recent SQL Server versions have introduced adaptive shallow cleanup approaches for large side tables, improving efficiency and reducing locking contention. While Azure SQL Database implementation details differ from on-premises SQL Server, the operational guidance remains consistent: auto-cleanup continues to receive improvements and provides better telemetry than manual approaches.

For environments experiencing these issues, the recommended workflow starts with read-only validation to quantify the scope of orphaned rows and backlog. This is followed by enabling auto-cleanup if disabled, then monitoring cleanup activity. If problems persist, the disable/re-enable CT approach offers a safer alternative to manual deletion scripts. Throughout this process, maintaining short snapshot transactions and implementing auditing helps prevent future occurrences while providing better visibility into system behavior.

The complexity of Change Tracking cleanup underscores the importance of understanding the interplay between retention policies, transaction isolation levels, and cleanup mechanisms. Organizations implementing Change Tracking should carefully consider their cleanup strategy, maintain proper auditing, and be prepared to diagnose orphaned row scenarios using the diagnostic approaches outlined above.