semaphore timeout in copy activity from Azure SQL DB to Blob Storage

Question

semaphore timeout in copy activity from Azure SQL DB to Blob Storage

John Aherne 516

Hello,

We are trying to copy a large table from an Azure SQL database to blob storage but we keep getting the below error. The table has about 9.4 million records in it.

Any ideas what is causing it?

Thanks!

Error:

Type=System.Data.SqlClient.SqlException,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.),Source=.Net SqlClient Data Provider,SqlErrorNumber=121,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=121,State=0,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.),},],''Type=System.ComponentModel.Win32Exception,Message=The semaphore timeout period has expired,Source=

HarithaMaddi-MSFT 10,156 Reputation points

2020-10-28T07:07:50.147+00:00

Hi @John Aherne ,

Welcome to Microsoft Q&A Platform.

I looked at similar issues from Customers in past and observed that if using Azure IR along with auto-resolve feature, please check by creating Azure IR in the same region as the database.
Also in some cases it is due to network issues as well, please check the network connectivity.

If the issue persists, please share pipeline run id, region and data factory name with us to investigate further on this.
HarithaMaddi-MSFT 10,156 Reputation points

2020-10-29T17:41:13.693+00:00

Hi @John Aherne ,

We have not received a response from you. Are you still facing the issue? If you found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Looking forward for your response!
John Aherne 516 Reputation points

2020-10-29T19:15:01.66+00:00

Hi @HarithaMaddi-MSFT ,
Sorry for the delay in getting back to you. I haven't had time to test out creating an Azure IR in the same region as the DB. However, The DB, Data Factory and Storage account are all in West US.
I should also add that it is only one table from that database giving us problems. We pull many others without issue.

Here is a run id from one of the failed copy activities: 2a089e1f-4fb4-46cd-82b1-f69e29680f5b

Thanks!
HarithaMaddi-MSFT 10,156 Reputation points

2020-10-30T12:12:43.3+00:00

Hi @John Aherne ,

Thanks for confirming and sharing the details. I will reach out to internal teams for additional investigation. Meanwhile, if you got a chance to test with Azure IR in same region as DB, let us know the outcome.

Thanks for your patience!
Anonymous

2020-10-30T13:58:20.74+00:00

Hi Haritha,

Today, I am also facing the same issue. My Azure IR, Data Factory and database are in same region but still facing the same error while copying data between 2 tables within a same database. Here is the Pipeline Run ID : 89d5c0e1-8c88-4d39-be2e-8719e35137dc
if it helps you guys to identify the issue.

Regards,
Surendra
HarithaMaddi-MSFT 10,156 Reputation points

2020-11-02T06:34:08.77+00:00

Thanks @John Aherne and @Anonymous for sharing more details. I reached out to Product team and they suggested that issue comes from Azure SQL query used in retrieving the data and it is causing unstable performance impact. Mitigation suggested is to increase the connection timeout in Linked service. Long term resolution can be to understand the details on query tuning.

Please suggest if this helps and let us know for further queries., we will be glad to assist.
John Aherne 516 Reputation points

2020-11-02T16:06:26.16+00:00

Hi @HarithaMaddi-MSFT ,
It does seem to be an issue with Azure SQL. We got the same error on a different Azure SQL database when one of our devs was running an insert statement through SSMS. It was just 7 records (Static insert, not from a query), and they got that error every single time. Inserting each record individually worked.