Data Sync Failing

Question

Data Sync Failing

Peter Quinn 0

I have a data sync in Azure, and it keeps failing now with the following error...

Cannot enumerate changes at the RelationalSyncProvider for table 'TableNameRemoved'. Check the inner exception for any store-specific errors.Inner exception: SqlException ID: 53cb4e24-3233-4255-9829-b6a075978e37, Error Code: -2146232060 - SqlError Number:40197, Message: The service has encountered an error processing your request. Please try again. Error code 40549. SqlError Number:40197, Message: The service has encountered an error processing your request. Please try again. Error code 40549. SqlError Number:0, Message: SQL error with code 0 For more information, provide tracing ID ‘4a6cb2ea-7ae3-42bd-9ce4-453e9e683fdd’ to customer support."

When Monitored this, it happens after 1 hour of trying to run the selectchanges query for the largest table, I have tried upping the DTU settings, but this has no effect, if I check usage at this point in the portal it is around 4%,

The statement it is trying to execute will have been running for 1 hour at this point, originally running in 25 statements, but when it ends 17, in a waiting state.

User's image

Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-23T14:08:56.25+00:00
Hi Peter Quinn,

Greetings!

As we understand that, you are encountering an issue with long-running transactions in Azure SQL Database. The error code 40549 indicates that the service has encountered an error processing your request due to a long-running transaction.

Here are some insights and potential solutions to address this issue:

Ensure that the query you are running is optimized. Long-running queries can often be improved by adding appropriate indexes, avoiding unnecessary joins, and simplifying the query logic.

One effective way to handle long-running transactions is to reduce the size of the batch. This can help in making the transaction shorter and more manageable. You can use the ROWS_PER_BATCH parameter to control the size of the batch.

Although you mentioned that increasing the DTU settings had no effect, it might be worth considering switching to a higher performance tier or using vCore-based purchasing models, which offer more granular control over compute and memory resources.

Use Azure SQL Database's built-in monitoring tools to track resource usage and identify any bottlenecks. This can help in understanding if there are any specific times or conditions under which the issue occurs.

Implement retry logic in your application to handle transient errors. This can help in automatically retrying the operation if it fails due to temporary issues.

Here are links to several parallel threads that may be helpful to you:

https://learn.microsoft.com/en-us/answers/questions/1488461/data-sync-issue-in-a-sync-group-sync-failed-with-t

https://learn.microsoft.com/en-us/answers/questions/974919/data-sync-group-wont-start-no-obvious-errors

I hope this information helps. If the issue still persists, could you please share the details requested via private message as it will help us conduct a more thorough investigation.
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-23T17:32:49.0466667+00:00

Hi @Peter Quinn,

Could you please provide the information requested in the following questions.

Have you had the opportunity to review and follow the steps provided in our previous response?

Is the sync group stuck in a processing state for an extended period and not responding to stop commands?

Are there other applications or users accessing the same database at that time?
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-24T08:43:05.1533333+00:00

Hi @Peter Quinn,

We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
Peter Quinn 0 Reputation points

2025-04-24T09:27:02.7433333+00:00

Hi,

The queries being used in Data Sync are Microsoft's it is not something we have customised, surely you know this if you are offering advice?

There is no indication of missing indexing, and we have tried running after updating relevant statistics and still it fails.

Regards

Peter

Your answer

Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-23T14:08:56.25+00:00

Hi Peter Quinn,

Greetings!

As we understand that, you are encountering an issue with long-running transactions in Azure SQL Database. The error code 40549 indicates that the service has encountered an error processing your request due to a long-running transaction.

Here are some insights and potential solutions to address this issue:

Ensure that the query you are running is optimized. Long-running queries can often be improved by adding appropriate indexes, avoiding unnecessary joins, and simplifying the query logic.

One effective way to handle long-running transactions is to reduce the size of the batch. This can help in making the transaction shorter and more manageable. You can use the ROWS_PER_BATCH parameter to control the size of the batch.

Although you mentioned that increasing the DTU settings had no effect, it might be worth considering switching to a higher performance tier or using vCore-based purchasing models, which offer more granular control over compute and memory resources.

Use Azure SQL Database's built-in monitoring tools to track resource usage and identify any bottlenecks. This can help in understanding if there are any specific times or conditions under which the issue occurs.

Implement retry logic in your application to handle transient errors. This can help in automatically retrying the operation if it fails due to temporary issues.

Here are links to several parallel threads that may be helpful to you:

https://learn.microsoft.com/en-us/answers/questions/1488461/data-sync-issue-in-a-sync-group-sync-failed-with-t

https://learn.microsoft.com/en-us/answers/questions/974919/data-sync-group-wont-start-no-obvious-errors

I hope this information helps. If the issue still persists, could you please share the details requested via private message as it will help us conduct a more thorough investigation.
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-23T17:32:49.0466667+00:00

Hi @Peter Quinn,

Could you please provide the information requested in the following questions.

Have you had the opportunity to review and follow the steps provided in our previous response?

Is the sync group stuck in a processing state for an extended period and not responding to stop commands?

Are there other applications or users accessing the same database at that time?
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-24T08:43:05.1533333+00:00

Hi @Peter Quinn,

We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
Peter Quinn 0 Reputation points

2025-04-24T09:27:02.7433333+00:00

Hi,

The queries being used in Data Sync are Microsoft's it is not something we have customised, surely you know this if you are offering advice?

There is no indication of missing indexing, and we have tried running after updating relevant statistics and still it fails.

Regards

Peter

Share via

Data Sync Failing

Your answer