ADF Copy data activity issue from SQL Managed Instance to ADLS (Operation on target Source to ADLS failed: Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException)

Question

Hello,

I am encountering issue with the Copy activity in ADF with error message as below:

Operation on target Source to ADLS failed: Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: '[adls folder path]/data_31ccf4f0-8158-4109-908a-9ef4d936cc7d_59742144-0846-4d35-bc76-79558ed27ad6_00000.parquet'.,Source=Microsoft.DataTransfer.Common,''Type=System.Data.SqlClient.SqlException,Message=Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.,Source=.Net SqlClient Data Provider,SqlErrorNumber=1205,Class=13,ErrorCode=-2146232060,State=52,Errors=[{Class=13,Number=1205,State=52,Message=Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.,},],'

Sometimes the run is successful but sometimes it will run into the error as mentioned above.

Can someone kindly help advise on it?

Thank you.

Answer 1

Hi @Jacky ,

Thanks for using Microsoft Q&A forum and posting your query.

As per the error message, the copy activity failed to upload a file to the specified ADLS folder path due to a deadlock issue on your source system. “Transaction was deadlocked” error occurs when two or more sessions are waiting to get a lock on a resource which has already locked by another session in the same blocking chain.  As a result, none of the sessions can be completed and SQL Server has to intervene to solve this problem. It gets rid of the deadlock by automatically choosing one of the sessions as a victim and kills it allowing the other session to continue. In such case, the client receives the above error.

This is error is being thrown by your source Azure SQL MI. In order to overcome this issue on ADF side you can do one of the following as workarounds:

1. Check if there are any other processes running at the same time that might be causing the deadlock issue. You can try running the copy activity at a different time when there are fewer processes running.
2. Try to set the appropriate isolation level in your copy activity source settings:
   Relevant doc:
   SQL Managed Instance as a source IsolationLevel Enum - Fields (Please refer to this doc to get better understanding of about the database isolation levels)
   

You can also refer to below additional resources which would help in understanding about the deadlocks and how you can design your database (source) to avoid such issues.

1. Analyze and prevent deadlocks in Azure SQL Database
2. Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database or Managed Instance?
3. Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Hope this info helps. Let us know if you have further query.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.