Troubleshoot: Azure Synapse Link for SQL initial snapshot issues

  • Article

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

Start by reviewing your configuration. For more information, see Configure your source Azure SQL database.

Symptom

The users can follow these resolution steps to investigate:

  • 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

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:

Screenshot from SQL Server Management Studio of the sample result set of the tables and columns from the changefeed.change_feed_tables system table.

  • 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.
  • Steps of the snapshot can take longer to complete than others. 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, see 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:

Screenshot from SQL Server Management Studio of the sample result set of the tables and columns from the changefeed.change_feed_tables system table, indicating retries.

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:

Screenshot of the results of the sys.dm_change_feed_errors dynamic management view including possible snapshot errors to investigate.

  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.

Related content