Troubleshoot: Azure Synapse Link for SQL initial snapshot issues

This article is a guide to troubleshooting issues with initial snapshot on Azure Synapse Link for Azure SQL Database and SQL Server.

Symptom

The users can follow the below resolution steps to investigate the below cases:

• Determine if a link connection snapshot state is stuck.
• Determine if snapshot related errors are observed after starting the link connection.
• Understand the progress of the initial snapshot for individual tables.

Resolution

Step 1: Query to get the current snapshot state for the tables included in the link connection

Connect to the source database enabled for Azure Synapse Link using SQL Server Management Studio or Azure Data Studio.

Run the following T-SQL command in the source database to list all the tables enabled for change feed and their snapshot-related columns from changefeed.change_feed_tables. In the results from the query, check the snapshot_phase column.

SELECT table_group_id, table_id, state, version, snapshot_phase, 
 snapshot_current_phase_time, snapshot_retry_count, snapshot_start_time,
 snapshot_end_time, snapshot_row_count 
FROM changefeed.change_feed_tables;

Example output:

• If the snapshot_phase column value for the desired table is 6 (EMIT_SNAPSHOT_ENDENTRY), it means snapshot has already completed on the table and needs no further investigation.
• There is a possibility that one of the phases of the snapshot may take longer to complete. Phase 5 (EXPORT_DATA_FILE) can be a time-consuming step. When the table size is large, the EXPORT_DATA_FILE phase is expected to take longer to finish. For more information on the snapshot phases, refer to changefeed.change_feed_tables.

When a snapshot has not completed for a given table, there are two possible cases to consider based on the output of changefeed.change_feed_tables:

• When Snapshot_phase < 6 and snapshot_retry_count = 0, the snapshot operation is ongoing, without error. No action is needed in this case, wait for snapshot completion.
• When Snapshot_phase < 6 and snapshot_retry_count > 0, the snapshot operation has been failing and is being retried. Proceed to Step 2. For example, as in the following image:

Step 2: Snapshot retry

If errors have forced the snapshot to retry, find more information in the sys.dm_change_feed_errors dynamic management view. Run the following T-SQL command in the source database:

SELECT * FROM sys.dm_change_feed_errors;

For example:

1. If any errors are observed from the snapshot component when source_task = 5, refer to error specific mitigation details in the Known limitations and issues with Azure Synapse Link for SQL.

2. If the error is not found in the known limitations article, submit an Azure support request following the below instructions:

   1. For Issue type, select Technical.
   2. Provide the desired subscription of the source database. Select Next.
   3. For Service type, select SQL Database.
   4. For Resource, select the source database where the initial snapshot is failing.
   5. For Summary, provide the error numbers from changefeed.change_feed_errors.
   6. For Problem type, select Data Sync, Replication, CDC and Change Tracking.
   7. For Problem subtype, select Transactional Replication.

   

Next steps

• Get started with Azure Synapse Link for Azure SQL Database
• Get started with Azure Synapse Link for SQL Server 2022
• Known limitations and issues with Azure Synapse Link for SQL