Lesson Learned #432: Resolving DataSync Failures in Azure SQL Database Caused by Custom Triggers
Azure SQL Database provides a robust DataSync service to synchronize data across multiple Azure SQL databases or between on-premises SQL Server and Azure SQL databases. While generally reliable, some exceptions can disrupt the smooth flow of data synchronization. One such error occurs when custom-defined triggers interfere with DataSync's internal processes, resulting in a failure like the one described below: Sync failed with the exception 'An unexpected error occurred when applying batch file sync_XXX\\XX-XXX-XYZ-afb1-XXXX.batch. See the inner exception for more details.Inner exception: Index was outside the bounds of the array. For more information, provide tracing ID ‘NNNN-3414-XYZ-ZZZ-NNNNNNNX’ to customer support.'Lesson Learned #369:Size NNN from table 'XYZ' cannot fit within the specified batch size of 24576KB
Today we had a new case where our client encountered the following error message when synchronizing tables using DataSync "The row of size 42530KB from table 'dbo.XYZ' cannot fit within the specified batch size of 24576KB". Following, I would like to share a possible workaround for it, moving the data to blob storage and reducing the size of the database and backups.
