ADF Copy Activity (from Azure DB to Azure Synapse Analytics) timeout expired

Question

Hi All,

We have faced the issue when Copy Activity (from Azure DB to Azure Synapse Analytics) failed into a timeout problem.

Key points:

1. The only one Copy Activity needs to load 110 million rows from Azure DB (data source a stored procedure that makes select from a view) to Azure Synapse Analytics (table).
2. Source: query timeout - 1440 minutes.
3. Sink: copy method - bulk insert (others are not available to copy from Azure DB).
4. Settings: DIU and Degree of copy parallelism are by default.

Results: connection with the source was lost after 5 hours of loading (around 90 million rows were copied).

Details of issue:
ErrorCode=SqlBatchWriteTransactionFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL transaction commits failed,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.,Source=.Net SqlClient Data Provider,SqlErrorNumber=-2,Class=11,ErrorCode=-2146232060,State=0,Errors=[{Class=11,Number=-2,State=0,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.,},],''Type=System.ComponentModel.Win32Exception,Message=The wait operation timed out,Source=,'

Run IDs can be added if it is needed.

Will be very happy if anyone could help us to resolve this issue.

Thanks in advance!

Answer 1

@Artem_A I found another explanation and solution. This one seems more appropriate.

By default, the SQL sink client sets a 2 hour timeout on the connection when writing to the database. When the batch being written takes more than 2 hours to complete, the connection times out before completion and returns an error

For this case, the solution is to set a Write batch size on the Sink settings so that the write is broken up into multiple batches that should complete within 2 hours each. 10,000 is a good starting number. Alternatively, you can set the Write batch timeout to a longer time than 02:00:00.

Answer 2

Hello @Artem_A and welcome to Microsoft Q&A. A failure after 5 hours, and so close to completion must be frustrating.

There are a number of things to try. Can you increase the retry count in the Copy activity? The default is zero (no retry). Increasing the count and retry interval should allow it to attemt to regain connection.

What is your Timeout set to? The default timeout on Copy activity is 7 days. (Too long in my opinion). If your timeout was set to 5 hours, then it was Data Factory which halted the copy. Otherwise the sink was having trouble.

Also please see the section entiteled ""Transfer - writing to sink" experienced long working duration:" in Copy Activity Performance Troubleshooting. It has some points specific to Synapse.