SQL connection timeout error in datafactory sql sink activity

Jijeesh Aliyan Kandy 1

I am getting frequent failures due to SQL connection timeout in data factory sink activity. The frequency of the failures are really high during concurrent pipeline execution using Tumbling window triggers. The detailed error message is like below.

Operation on target df_fac_cac_acg_entr_hist failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: Connection timed out (Read failed)","Details":"shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Connection timed out (Read failed)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3206)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2066)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6617)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7805)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7768)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1589)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$300(SQLServerBulkCopy.java:65)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:663)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)\n\tat shaded.m"}

Pratik Somaiya 4,201 Reputation points

2022-03-30T04:10:11.35+00:00

Hello @Jijeesh Aliyan Kandy

This error usually occurs due to memory limit of TEMP DB

What is the tier of the SQL DB that you are using? Database tier affects performance

There are two similar articles that you can get a help from:

https://learn.microsoft.com/en-us/answers/questions/435182/adf-connection-timed-out.html

https://learn.microsoft.com/en-us/answers/questions/423702/azure-data-factory-tempdb-usage-in-data-flow.html
Jijeesh Aliyan Kandy 1 Reputation point

2022-03-30T07:10:16.74+00:00

Hi @Pratik Somaiya

Thanks for providing an answer on this.

I use Azure SQL DB Hyperscale Gen5 vCore32. It has 1TB of TempDB capacity.

I usually monitor the TempDB usage during my pipeline runs, the usage never went beyond 300GB. in addition to this I have kept the datafactory sink TempDB option unchecked, means the sink creates the stage table under the user database instead of TempDB.

I have observed that the frequency of this issue is really high on some days, not sure if anything going wrong with the data transfer though n/w.

@Balasubramanian, N (Nagarajan)
@Poel van der, RE (Ron)
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-04-01T04:49:00.557+00:00

Hello @Jijeesh Aliyan Kandy ,

This issue requires a deeper investigation to identify the exact root cause. If you have a support plan, please do file a support ticket else please let us know here so that we can check on other alternate option to help unblock you.

Thank you
Jijeesh Aliyan Kandy 1 Reputation point

2022-04-01T07:36:35.65+00:00

Hi @KranthiPakala-MSFT ,

Yes, I have raised an incident ticket with Microsoft support. It is still under investigation.

Thanks
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-04-01T22:54:48.493+00:00

Hello @Jijeesh Aliyan Kandy ,

Thanks for confirming. Would you please share the SR# with us here for tracking it internally? Also please do consider sharing the root cause and resolution here once the support ticket is closed, as it can be beneficial for others reading this thread.

Thank you