Data Factory activities stuck in Queued

Question

Data Factory activities stuck in Queued

Daniel Troyer 1

Copy task is invoked as a child activity and stays in "queued" state for over an hour. This is intermittent and resembles a bug identified in version 5.2.

Environment:
Azure Data Factory
Linked Service: Postgres (vnet access)
Linked Service: ADLS Gen 2
Linked Service: Azure SQL Database
IR Type: Self Hosted
IR Version: 5.23.8355.1

Scenario:
Parent pipeline has a Lookup then a For loop containing an Execute Pipeline activity.
Child pipeline has a Lookup then a For loop containing a series of Copy and Script activities.

When debugging child pipeline with static parameters, it tends to run well most of the time.
When debugging the parent pipeline, the child pipeline runs pretty well after a fresh reboot of the IR, but after about a day, the third activity (copy action) hangs in the queued state longer and longer. We have over-provisioned the IR host with resources to overcome this, but the problem continues.

This problem happens regardless of the "queue length" on the IR. Occurs both when there is other activity and when there is no other activity.

Pipeline Run id: 4f25f845-2edf-4811-9d6d-0df6d0d5df17
Activity run id: 2dea7b9f-9211-4360-b44e-23f832c4373a

Daniel Troyer 1 Reputation point

2022-12-13T18:37:56.717+00:00

Activity finally failed:

Error code:
SystemErrorActivityRunExecutedMoreThanOnce
Failure type:
System error
Details:
ErrorCode=SystemErrorActivityRunExecutedMoreThanOnce,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The activity run failed due to service internal error, please retry this activity run later.,Source=Microsoft.DataTransfer.TransferTask,'
Source:
Pipeline Read_table

The documentation linked to the error does not have an explanation for the error.
Daniel Troyer 1 Reputation point

2022-12-13T18:41:03.357+00:00

Interestingly enough, whenever I run the debug directly on the child pipeline, it often runs fine. When I run the debug on the parent, then the child hangs and fails.

Running additional permutations of the child-only pipeline is starting to hang too.
Daniel Troyer 1 Reputation point

2022-12-13T18:57:15.053+00:00

Today's reboot did not help the problem with the parent-child pipeline run. It hung and failed.
KranthiPakala-MSFT 46,612 Reputation points Microsoft Employee

2022-12-14T17:08:10.743+00:00

Hello @Daniel Troyer ,

Thanks for the question and using MS Q&A platform.

This issue seems strange and sorry for your experience. Child pipeline runs fine when debug run alone but fails when run through parent pipeline.
Have you also tested using the trigger run option or just the debug run? In not could you please give a try with trigger run the parent pipeline and see what is the behavior? Irrespective, the initial issue needs a deeper investigation to better understand the root cause as the error message says The activity run failed due to service internal error, please retry this activity run later which means something on the ADF service side is causing this. Hence would recommend filing a support ticket. If you have already filed a support ticket, please do share the Support ticket # and in case if you don't have a support plan, please let me know here.

Another option I would like you to give a try is please change or point the Azure IR to a location IR and see how the behavior would be, that would give a hint if something is wrong with the default Azure IR for your location.

Do let us know how it goes.

Thank you
Daniel Troyer 1 Reputation point

2022-12-14T19:30:43.273+00:00

I'll file a support ticket today. Also going to try an older version of the IR software.

I worked with a DBA today and we discovered that the PID on the database is never being created for the sessions that fail so I suspect something is buggy in the Postgres connector. It works sometimes using triggers and debug runs but often one of the three children will run and hang without ever establishing a proper DB session with Process ID.
KranthiPakala-MSFT 46,612 Reputation points Microsoft Employee

2022-12-15T18:52:10.143+00:00

Hello @Daniel Troyer ,

Yes, that sounds buggy. To confirm if it is a problem with Postgres connector or something else, we need a deeper investigation in the backend. Please do file a support ticket and share the SR# here with us for tracking it internally.

Thank you
KranthiPakala-MSFT 46,612 Reputation points Microsoft Employee

2022-12-22T23:23:56.41+00:00

Hello @Daniel Troyer ,

We still have not heard back from you. Just wanted to check if you have got a chance to file a support ticket for this issue, if so, could you please share the SR# here for tracking it. In case if you have already found root cause and solution, would you please share the details here so that others get benefitted reading this thread. Moreover, if you don't have a support plan and you are blocked, please let us know.

Your answer

Daniel Troyer 1 Reputation point

2022-12-13T18:37:56.717+00:00

Activity finally failed:

Error code:
SystemErrorActivityRunExecutedMoreThanOnce
Failure type:
System error
Details:
ErrorCode=SystemErrorActivityRunExecutedMoreThanOnce,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The activity run failed due to service internal error, please retry this activity run later.,Source=Microsoft.DataTransfer.TransferTask,'
Source:
Pipeline Read_table

The documentation linked to the error does not have an explanation for the error.
Daniel Troyer 1 Reputation point

2022-12-13T18:41:03.357+00:00

Interestingly enough, whenever I run the debug directly on the child pipeline, it often runs fine. When I run the debug on the parent, then the child hangs and fails.

Running additional permutations of the child-only pipeline is starting to hang too.
Daniel Troyer 1 Reputation point

2022-12-13T18:57:15.053+00:00

Today's reboot did not help the problem with the parent-child pipeline run. It hung and failed.
KranthiPakala-MSFT 46,612 Reputation points Microsoft Employee

2022-12-14T17:08:10.743+00:00

Hello @Daniel Troyer ,

Thanks for the question and using MS Q&A platform.

This issue seems strange and sorry for your experience. Child pipeline runs fine when debug run alone but fails when run through parent pipeline.
Have you also tested using the trigger run option or just the debug run? In not could you please give a try with trigger run the parent pipeline and see what is the behavior? Irrespective, the initial issue needs a deeper investigation to better understand the root cause as the error message says The activity run failed due to service internal error, please retry this activity run later which means something on the ADF service side is causing this. Hence would recommend filing a support ticket. If you have already filed a support ticket, please do share the Support ticket # and in case if you don't have a support plan, please let me know here.

Another option I would like you to give a try is please change or point the Azure IR to a location IR and see how the behavior would be, that would give a hint if something is wrong with the default Azure IR for your location.

Do let us know how it goes.

Thank you
Daniel Troyer 1 Reputation point

2022-12-14T19:30:43.273+00:00

I'll file a support ticket today. Also going to try an older version of the IR software.

I worked with a DBA today and we discovered that the PID on the database is never being created for the sessions that fail so I suspect something is buggy in the Postgres connector. It works sometimes using triggers and debug runs but often one of the three children will run and hang without ever establishing a proper DB session with Process ID.
KranthiPakala-MSFT 46,612 Reputation points Microsoft Employee

2022-12-15T18:52:10.143+00:00

Hello @Daniel Troyer ,

Yes, that sounds buggy. To confirm if it is a problem with Postgres connector or something else, we need a deeper investigation in the backend. Please do file a support ticket and share the SR# here with us for tracking it internally.

Thank you
KranthiPakala-MSFT 46,612 Reputation points Microsoft Employee

2022-12-22T23:23:56.41+00:00

Hello @Daniel Troyer ,

We still have not heard back from you. Just wanted to check if you have got a chance to file a support ticket for this issue, if so, could you please share the SR# here for tracking it. In case if you have already found root cause and solution, would you please share the details here so that others get benefitted reading this thread. Moreover, if you don't have a support plan and you are blocked, please let us know.

Share via

Data Factory activities stuck in Queued

Your answer