ADF Copy Activity hangs when writting to SQL Server

Question

Hello,

I have been stuck on an issue for several days that I have no idea what's happened.

So I have a copy activity in an ADF pipeline that copies data from a staging table in a DWH (Azure SQL Database) to a production SQL server. Originally there are around 3 million rows to copy over. The Detail page shows "Data read" and "Rows read" and "Interim data written" and "Interim rows written" which seemed to have no issues. However, the "Rows witten" and "Data written" show 0 bytes, and the copy activity kept hanging until either it times out, or being cancelled.

Thinking it might be because there are too much rows, I have cut down to get rows within 2 years, still did not work. Then I cut down again to rows within 1 year and it successfully upserted to the sink. Then I tried 1 year and 3 months, it worked. And then I tried 1 year and 4 months, it hang.

So I go back and check the data in the 2 columns that would be used as key for upsert. There are no Null or invalid value.

The thing was if the pipeline was timed out or cancelled, even in debug mode, there is no Log for me to look at. The activity it self didn't throw error, it was just "in progress".

I check on the sink side and create a duplicate of the target table to test, still doing the same thing. There is no table locking that I see.

Is there any thing that I can do to find out the issue? Appreciate any helps.

Answer 1

@Ha Thai

Welcome to Microsoft Q&A platform and thanks for posting your query here.

Please check below steps to troubleshoot and resolve the issue:

1. Check for Data Anomalies: Ensure there are no null values or unexpected data types in your source data. Sometimes, data anomalies can cause the copy activity to hang
2. Monitor Performance: Use the performance tuning tips provided by Azure Data Factory to identify any bottlenecks. This can help you understand if the issue is related to data store throttling, integration runtime performance, or other factors
3. Increase DTUs: If your SQL Server is under high utilization, consider upgrading to a higher tier to handle the load better
4. Check for Table Locks: Even though you mentioned there are no table locks, it's worth double-checking if there are any long-running transactions or locks on the target table that might be causing the issue
5. Review Integration Runtime: Ensure that your integration runtime has enough resources to handle the data load. If you're using a self-hosted IR, consider scaling it up or out
6. Enable Logging: Configure logging in your ADF pipeline to capture more detailed logs. This can help you identify where the process is getting stuck.
7. Test with Smaller Batches: As you've already done, continue testing with smaller batches of data to pinpoint the exact threshold where the issue occurs. This can help you identify if there's a specific data-related issue.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.