Data Sync has been failing for over a week

Peter Quinn 20 Reputation points

Data Sync Sync_To_Reporting_Database on Database SDPriceIT has been failing for over a week, I have tried increasing the DTU settings to 3000 and it has still failed. Please advise what

Failure looks to be a timeout..

Check the inner exception for any store-specific errors.Inner exception: SqlException ID: a26359fc-c939-401a-bf69-34fd416e1fa3, Error Code: -2146232060 - SqlError Number:-2, Message: SQL error with code -2 Inner exception: The wait operation timed out For more information, provide tracing ID ‘4de4cd19-2508-490d-a9df-bfcc41cd3081’ to customer support."

  1. Manoj Kumar Boyini 17,060 Reputation points • Microsoft External Staff • Moderator

    Hi @Peter Quinn

    During the sync process, the service retrieves the database schema and stores it in the Sync Metadata Database. If this metadata database is running on a lower performance tier or is under load, it can lead to timeout errors especially when the schema is large or complex.

    This behavior is also documented in Microsoft guidance: Azure SQL Data Sync best practices clearly mentions that schema refresh operations may fail with timeouts if the metadata database is on a low SKU, and recommends scaling it up.

    Recommended Action

    Scale up the Sync Metadata Database (SyncDB) to a higher performance tier
    Once the sync schema operation completes successfully, you can scale it back down if needed
    If future schema refresh operations are required, you may need to scale up again

    Please let us know if you have any questions or concerns.

  2. Peter Quinn 20 Reputation points

    It's on Standard tier and I have tried scaling up to DTU 3000 after updating statistics on both Databases

Sign in to comment

Answer accepted by question author

Manoj Kumar Boyini 17,060 Reputation points • Microsoft External Staff • Moderator

Hi @Peter Quinn

Thank you for the update. Since the synchronization completed successfully after removing table from the Sync Group, this confirms that the issue was isolated to that table. If preserving the data is required, you may consider renaming the existing table to an archive table and creating a new table with the same schema before adding it back to the Sync Group. This approach avoids the need to copy a very large volume of data and can save considerable time while maintaining a backup of the original records.

Please let us know if you have any questions or concerns

0 comments No comments
Sign in to comment

2 additional answers

  1. Mohamed Baioumy 0 Reputation points • Microsoft Employee

    Thank you for your patience, and I really appreciate you sharing the details with us. I’ve taken some time to analyze the error along with the Azure SQL Database setup, and I’d like to walk you through what we’re seeing.

    What we’re observing

    The failure is happening during the change enumeration phase on the table dbo.SDFoxFndDocumentPriceDetailStaging.

    In simple terms, this is the stage where Data Sync tries to read and identify the changes that need to be synchronized. During this process, the underlying query is taking longer than expected and eventually hits a timeout (SQL error -2).

    This indicates a performance-related bottleneck, rather than a configuration issue or a functional problem with Data Sync itself.

    What could be contributing to this

    Based on the behavior and the table involved, this is most likely related to one or more of the following:

    • The table holds a large volume of data or is frequently updated
    • Indexes may be missing or not optimal, making the query slower
    • There could be blocking or concurrent activity affecting query execution
    • A large backlog of change tracking data that needs to be processed

    Initial checks we recommend

    To better understand the situation, it would be helpful to review:

    1. Table size

    SQL

    sp_spaceused 'dbo.SDFoxFndDocumentPriceDetailStaging'

    2. Change tracking volume

    SQL

    SELECT COUNT(*)

    FROM dbo.SDFoxFndDocumentPriceDetailStaging_tracking

    Design observation

    I also noticed that the database TPM_Reporting_Database is currently acting as both:

    • a member database, and
    • the sync metadata database

    While this is technically possible, it is not considered a best practice. For better performance and isolation, we recommend hosting the sync metadata database in a separate, dedicated Azure SQL Database.

    Summary

    Overall, this looks like a performance constraint during the change detection step, particularly for the staging table mentioned above. Focusing on table size, indexing, and workload patterns should help us pinpoint the exact cause and improve reliability.

    Please feel free to share the results from the checks above, and I’ll be more than happy to review them with you and suggest the next steps.

    Thanks again for your collaboration on this—happy to continue working together on it

    1. Deleted

      This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Sign in to comment
  2. Alberto Morillo 35,506 Reputation points • MVP • Volunteer Moderator

    Could you please perform index rebuild and update statistics on the production databases involved in the sync? You can use Ola Hallengren scriptsif you want. After that try to run the sync process again.

    If you still receive the same error, use sp_who2 or this article to see any blocking process not allowing the sync to work.

    1. Peter Quinn 20 Reputation points

      I've tried updating statistics but problem persists.

    Sign in to comment
Sign in to answer

Your answer