Introduction

Azure SQL Data Sync is a popular solution for synchronizing data across multiple Azure SQL Database instances. It uses Change Tracking to identify and propagate data modifications between participating databases. While Data Sync can operate reliably for extended periods, environments with highly active tables may occasionally encounter synchronization failures that become increasingly difficult to recover from.

In this article, we examine a real-world troubleshooting scenario in which Azure SQL Data Sync repeatedly failed while attempting to synchronize changes for a specific table. The investigation revealed that excessive synchronization metadata growth and a large change backlog were causing change enumeration operations to exceed Azure SQL Database resource governance thresholds, resulting in repeated synchronization failures.

This post explains the symptoms, investigation process, troubleshooting scripts, root cause, mitigation strategy, and preventive measures that administrators can apply in their own Azure SQL Data Sync environments.

Symptoms

The issue manifested as repeated Azure SQL Data Sync failures for a single synchronized table while the sync group remained unhealthy.

Administrators may encounter errors similar to:

Cannot enumerate changes at the RelationalSyncProvider.

SqlError Number: 40197

The service has encountered an error processing your request.

Please try again.

Error code 40549

These errors can occur when Data Sync attempts to enumerate pending changes through Change Tracking and synchronization metadata, but the operation becomes excessively resource intensive.

Additional Warning Signs

• Synchronization runs taking significantly longer than usual
• Repeated synchronization retries
• Increasing synchronization latency
• Large Data Sync metadata growth
• Sync groups reporting warning or failed states

Understanding How Azure SQL Data Sync Uses Change Tracking

Azure SQL Data Sync relies on SQL Change Tracking to identify modifications occurring within synchronized tables.

The synchronization architecture generally consists of:

Hub Database

The central synchronization endpoint responsible for orchestrating synchronization.

Member Databases

Databases that participate in synchronization and exchange data with the hub.

Change Tracking

Tracks data modifications and provides an efficient mechanism to identify rows that have changed since the last synchronization cycle.

Synchronization Metadata

Data Sync maintains internal metadata used to track synchronization state and determine which changes must be applied.

Change Enumeration

During synchronization, Azure SQL Data Sync enumerates tracked changes and applies them across participating databases.

As synchronization backlog grows, the complexity and duration of enumeration operations increase accordingly.

Investigation Process

The troubleshooting effort focused on identifying where synchronization was failing and determining whether the underlying issue was related to Change Tracking, synchronization metadata, or Azure SQL resource limitations.

Step 1 – Identify the Failing Object

Review synchronization logs to determine which table repeatedly generates failures.

Step 2 – Determine Where the Failure Occurs

Determine whether the issue originates from:

• Hub Database
• Member Database
• Synchronization Infrastructure

Step 3 – Evaluate Synchronization Backlog

Assess the volume of pending changes and synchronization metadata growth.

Step 4 – Assess Resource Governance Impact

Evaluate whether Azure SQL Database resource governance may be terminating synchronization-related operations.

Useful T-SQL Scripts for Azure SQL Data Sync Troubleshooting

During the investigation, several T-SQL queries were used to validate Change Tracking configuration, evaluate synchronization backlog size, identify governance-related interruptions, and assess the overall health of the Azure SQL environment.

All scripts below have been sanitized and generalized for public use.

Note: Replace dbo.SyncTable with the affected synchronized table in your environment.

1. Verify Whether Change Tracking Is Enabled

Azure SQL Data Sync requires Change Tracking to function correctly.

Check Database-Level Change Tracking

-- Run to Master DB
SELECT
    DB_NAME(database_id) AS DatabaseName,
    is_auto_cleanup_on,
    retention_period,
    retention_period_units_desc
FROM sys.change_tracking_databases
WHERE database_id = DB_ID();

Check Table-Level Change Tracking

-- Run to UserDB
SELECT
    OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
    OBJECT_NAME(object_id) AS TableName,
    begin_version,
    cleanup_version,
    min_valid_version
FROM sys.change_tracking_tables
WHERE object_id = OBJECT_ID('dbo.SyncTable');

Why This Matters

If Change Tracking is disabled, Azure SQL Data Sync cannot enumerate changes successfully.

2. Estimate Synchronization Backlog Size

One of the most useful troubleshooting indicators is the volume of pending changes.

--Run to UserDb
SELECT COUNT(*) AS PendingChanges
FROM CHANGETABLE(CHANGES dbo.SyncTable, 0) AS CT;

Why This Matters

A very large backlog may indicate:

• Synchronization delays
• Metadata accumulation
• Enumeration pressure
• Increased risk of governance-related failures

3. Review Change Tracking Metadata

--Run to UserDB
SELECT
  OBJECT_NAME(object_id) AS TableName,
    begin_version,
    min_valid_version,
    cleanup_version
FROM sys.change_tracking_tables
WHERE object_id = OBJECT_ID('dbo.SyncTable');

Why This Matters

This information helps determine whether Change Tracking metadata is growing faster than cleanup processes can manage.

4. Validate Database Service Tier

-- Run to UserDB
SELECT 
 database_id, 
 edition, 
 service_objective, 
 elastic_pool_name 
FROM sys.database_service_objectives;

Why This Matters

Resource limitations associated with a database service tier may contribute to synchronization instability under heavy workloads.

5. Check for Resource Governance Events

Run the following query from the master database of the Azure SQL logical server.

SELECT TOP 20 
	end_time, 
	event_type, 
	event_subtype_desc, 
	description 
FROM sys.event_log 
WHERE event_type = 'connection'   
	AND event_subtype_desc = 'killed_by_governance'   
	AND end_time > DATEADD(hour, -24, GETUTCDATE()) 
ORDER BY end_time DESC;

Why This Matters

This query can reveal whether Azure SQL Database terminated operations because they exceeded governance thresholds.

Examples include:

• Long-running synchronization queries
• Excessive CPU consumption
• Excessive IO workload
• Large Change Tracking enumeration operations

Note: sys.event_log is available only from the master database.

6. Identify Large Tables

SELECT
    t.name AS TableName,
    SUM(p.rows) AS RowCounts
FROM sys.tables t
INNER JOIN sys.partitions p
    ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
GROUP BY t.name
ORDER BY RowCounts DESC;

Why This Matters

Large high-churn tables often generate substantial amounts of synchronization metadata and are frequently associated with Data Sync performance issues.

7. Review Change Tracking Across All Tables

SELECT
    OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
    OBJECT_NAME(object_id) AS TableName,
    begin_version,
    cleanup_version,
    min_valid_version
FROM sys.change_tracking_tables
ORDER BY TableName;

Why This Matters

This query helps identify whether metadata growth is isolated to a single synchronized table or occurring across multiple tables.

Troubleshooting Checklist

When troubleshooting Azure SQL Data Sync failures:

• Confirm Change Tracking is enabled.
• Identify the failing synchronized table.
• Measure synchronization backlog size.
• Review Change Tracking metadata.
• Check database service-tier configuration.
• Check Azure SQL governance events.
• Review table size and update patterns.
• Monitor resource utilization.
• Validate synchronization health following remediation.

Root Cause Analysis

The investigation ultimately revealed several contributing factors:

1. A large synchronization backlog accumulated over time.
2. Change Tracking metadata continued to grow.
3. Synchronization enumeration operations became increasingly resource intensive.
4. Azure SQL Database resource governance began terminating long-running synchronization operations.
5. Data Sync repeatedly retried synchronization and encountered the same failures.

As a result, synchronization was unable to progress beyond the accumulated backlog and remained stuck in a failure cycle.

Resolution

The mitigation focused on reducing synchronization pressure and rebuilding synchronization state.

Recovery Approach

1. Remove the affected table from the Sync Group.
2. Save synchronization configuration changes.
3. Preserve business data through backups or archival.
4. Reset or recreate the synchronized table when appropriate.
5. Allow synchronization metadata cleanup.
6. Re-add the table to the Sync Group.
7. Trigger synchronization.
8. Validate successful synchronization completion.

Following this approach, synchronization resumed successfully without further failures.

Why the Resolution Works

This process addresses the underlying metadata problem rather than repeatedly retrying synchronization.

Benefits include:

• Cleanup of excessive synchronization metadata
• Elimination of accumulated backlog
• Reset of synchronization state
• Fresh synchronization initialization
• Reduced enumeration workload

Technical Recommendations

1. Monitor synchronization health regularly.
2. Track synchronization latency.
3. Observe Data Sync metadata growth.
4. Investigate synchronization failures early.
5. Monitor DTU or vCore utilization.
6. Review high-volume synchronized tables.
7. Validate Change Tracking health periodically.
8. Monitor retries and failed sync operations.
9. Establish proactive alerting.
10. Review synchronization design for large-scale workloads.

Common Error Messages

Error 40197

The service has encountered an error processing your request.

Please try again.

Potential Causes

• Transient platform interruption
• Resource-governance intervention
• Long-running synchronization operations

Error 40549

Error code 40549

Potential Causes

• Excessive resource consumption
• Long-running transactions
• Synchronization enumeration pressure

Cannot Enumerate Changes at the RelationalSyncProvider

Cannot enumerate changes at the RelationalSyncProvider.

Potential Causes

• Change Tracking backlog growth
• Excessive synchronization metadata
• Resource-governance intervention

Lessons Learned

• Monitor Data Sync metadata growth proactively.
• Investigate synchronization delays before backlog accumulates.
• High-volume transactional tables require closer monitoring.
• Resource governance can significantly impact synchronization workloads.
• Reinitializing synchronization state may be necessary when metadata growth becomes excessive.

Key Takeaways

• Azure SQL Data Sync depends heavily on Change Tracking metadata.
• Large synchronization backlogs can cause expensive enumeration operations.
• Errors 40197 and 40549 may indicate resource-governance interruptions.
• Large metadata accumulation can trigger synchronization failures.
• Monitoring synchronization health is essential.
• High-volume tables require ongoing review.
• Resetting synchronization state can be an effective recovery mechanism.

Conclusion

Azure SQL Data Sync remains a powerful solution for synchronizing data across Azure SQL Database environments. However, synchronization metadata and Change Tracking backlog growth can gradually evolve into serious operational challenges when left unchecked.

In this troubleshooting scenario, synchronization failures were ultimately traced to a combination of excessive Change Tracking backlog growth and Azure SQL Database resource-governance limits. By identifying the affected table, measuring backlog pressure using targeted T-SQL queries, evaluating governance events, and reinitializing synchronization state, synchronization was successfully restored and stabilized. 

The key lesson is simple: proactive monitoring of Change Tracking metadata, synchronization backlog size, and Azure SQL workload health can prevent many Data Sync outages before they become business-impacting incidents.