Actually, we have one pipeline with 30 different schedules based on different pipeline parameter. Some of schedule executed every 10 min and some of executed once in a day. After setup the pipeline it running fine for one day. But after that suddenly all pipeline trigger run stuck in In-progress status and I am not able to find any root cause. So can anyone help me How we can find the root cause or logs. Also how we resolve the issue? approx more than 5000 trigger schedule with In-progress status. Please anyone provide solution asap.

Thank you for the information @NikunjPatel-2604 . Given that all these pipelines are In-Progress, I suspect some resource is not responding, and the activities are left waiting for answer. They will wait until the Timeout property is reached. Since you are using a database, the next step in finding root cause, is to look at whether the database is actually executing the requests. You are using lookups and stored procs. First let us find out which step the pipeline is stuck on. Go to the Data Factory Monitoring section, and find one of the stuck pipeline runs. Click on the name and it should show you the status of the activities. From this you should be able to determine whether it is lookup or stored proc that is stuck. Get with your database person and check the logs in the database as to what happened to the request database side. Did the request execute? Did the request get stuck? Or did the request never make it into the database? For the immediate situation, I recommend you make 2 changes to the pipeline (for now). Set the pipeline concurrency to 100, or some number you are comfortable with. If no number is used, more and more will be triggered. The concurrency is total for the pipeline, not per trigger, but until the situation is stabilized, it doesn't matter. Set the Timeout property of the Stored Proc and Lookup activities to an hour or some value you are comfortable with. The default value is 7 days, meaning that the activity waits 7 days before giving up and failing. Reducing this value will cause new executions to expire sooner. To stop all the currently executing runs, we can use Powershell (or other methods) to get the list of pipeline runs and stop each. Get-AzDataFactoryV2PipelineRun stop-azdatafactoryv2pipelinerun Get all the runs of the pipeline in a time window: $list =Get-AzDataFactoryV2PipelineRun -ResourceGroupName <RgName> -DataFactoryName <FactoryName> -PipelineName <ThatPipelinename> -LastUpdatedAfter <startedDate> -LastUpdatedBefore <nowDate> Get the RunId's of those that are in-Progress: $toStop = $list.ForEach({ if( $_.Status -eq "InProgress") { $_.RunId }} ) Stop them by RunId: $toStop.ForEach({ Stop-AzDataFactoryV2PipelineRun -ResourceGroupName <RgName> -DataFactoryName <FactoryName> -PipelineRunId $_ })

Azure Data factory V2 pipeline trigger stuck with In-progress status?

Accepted answer

MartinJaffer-MSFT 26,091 Reputation points

2020-07-07T20:37:20.13+00:00
Thank you for the information @NikunjPatel-2604 .

Given that all these pipelines are In-Progress, I suspect some resource is not responding, and the activities are left waiting for answer. They will wait until the Timeout property is reached.
Since you are using a database, the next step in finding root cause, is to look at whether the database is actually executing the requests. You are using lookups and stored procs. First let us find out which step the pipeline is stuck on.
Go to the Data Factory Monitoring section, and find one of the stuck pipeline runs. Click on the name and it should show you the status of the activities. From this you should be able to determine whether it is lookup or stored proc that is stuck.
Get with your database person and check the logs in the database as to what happened to the request database side. Did the request execute? Did the request get stuck? Or did the request never make it into the database?

For the immediate situation, I recommend you make 2 changes to the pipeline (for now).

Set the pipeline concurrency to 100, or some number you are comfortable with. If no number is used, more and more will be triggered. The concurrency is total for the pipeline, not per trigger, but until the situation is stabilized, it doesn't matter.

Set the Timeout property of the Stored Proc and Lookup activities to an hour or some value you are comfortable with. The default value is 7 days, meaning that the activity waits 7 days before giving up and failing. Reducing this value will cause new executions to expire sooner.

To stop all the currently executing runs, we can use Powershell (or other methods) to get the list of pipeline runs and stop each.

Get-AzDataFactoryV2PipelineRun
stop-azdatafactoryv2pipelinerun

Get all the runs of the pipeline in a time window: $list =Get-AzDataFactoryV2PipelineRun -ResourceGroupName <RgName> -DataFactoryName <FactoryName> -PipelineName <ThatPipelinename> -LastUpdatedAfter <startedDate> -LastUpdatedBefore <nowDate>
Get the RunId's of those that are in-Progress: $toStop = $list.ForEach({ if( $_.Status -eq "InProgress") { $_.RunId }} )
Stop them by RunId: $toStop.ForEach({ Stop-AzDataFactoryV2PipelineRun -ResourceGroupName <RgName> -DataFactoryName <FactoryName> -PipelineRunId $_ })
Please sign in to rate this answer.
Nikunj Patel 186 Reputation points

2020-07-08T06:41:19.093+00:00

Thank you for your answer. It great helps to us. We found like one procedure executed long time and because of this remaining pipeline will wait for completion.

Also we need one more things like how we can achieve below scenario in ADF as mention in above comment.

Each pipeline take different different execute time based on parameter. Some time some one instance of pipeline is in-progress and second instance will start for same job parameter. Can we restrict this scenario in pipeline like if pipeline already executed with parameter X then next schedule for X parameter not executed until first instance will complete. But same pipeline with Y parameter instance need to executed as per schedule as parameter is different.

Nikunj Patel 186 Reputation points

2020-07-08T06:44:01.653+00:00

What is an impact of concurrency job executed setting at node in self hosted Integration run time. currently it configured value 14. Can we make it to 50?

Nikunj Patel 186 Reputation points

2020-07-08T06:51:09.6+00:00

Can we set timeout property of procedure component dynamically based on procedure name. As such procedure taking long time but it executed once in a day. And such procedure completed less than 1 min but it executed multiple times in a day.

MartinJaffer-MSFT 26,091 Reputation points

2020-07-08T20:52:35.26+00:00

If it is the parameters are unique to the triggers (no 2 triggers use same parameter), then i think you could accomplish this by changing the scheduled trigger into a tumbling window trigger.

Another option would be to simulate a mutex / lock / semaphore to indicate whether the job is busy or ready.
.
Suppose for given parameter X, we have a resource whose existence signifies the job is available to run (not already in progress). The resource could be a blob or database table.
In the beginning of your pipeline, a validation activity is used to check for the existence of resource X. The validation activity has the ability to poll for the existence of X, or timeout.
If resource X is not found, the pipeline should stop/fail.
If resource X is found, then the pipeline should delete resource X. Deleting resource X signifies the job is busy. Following that, run the job like you currently have. When job is finished, create resource X again to signify the job is ready for the next run.

MartinJaffer-MSFT 26,091 Reputation points

2020-07-08T20:56:13.717+00:00

No the Timeout of stored proc cannot be set dynamically. It is static.

MartinJaffer-MSFT 26,091 Reputation points

2020-07-08T21:04:21.987+00:00

The concurrent job limit on integration runtime, is how many jobs each node should be allowed. Read more here.
The SHIR can be very resource intensive. Exercise caution if you increase the limit. You may be better off increasing the number of nodes.

Limit. Limit signifies the maximum concurrent jobs for each node. This value is defined based on the machine size. You can increase the limit to scale up concurrent job execution in advanced scenarios, when activities are timing out even when CPU, memory, or network is under-utilized. This capability is also available with a single-node self-hosted integration runtime.

Nikunj Patel 186 Reputation points

2020-07-09T04:49:50.17+00:00

It means there is no direct facility in ADF to restrict this scenario. We have to manually handled in pipeline and we can achieve this.

Nikunj Patel 186 Reputation points

2020-07-09T04:56:02.67+00:00

In our pipeline mainly use for executing the store procedures and all the calculation is under store procedures. Because of this ADF wont need any processing of data and CPU utilization is under 20% only. Based on this we can increase the concurrency value.

Nikunj Patel 186 Reputation points

2020-07-09T04:56:35.253+00:00

Ok. Thank you for all your answers. Its clear many concerns about ADF.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data factory V2 pipeline trigger stuck with In-progress status?

0 additional answers

Your answer