Azure Data Factory executes stored procedure twice (and concurrently)

Matthias Delafontaine 16

I have an ADF pipeline executing a stored procedure on an Azure SQL database.

Retries is set to 0, so this procedure should be executed exactly once. Now it turns out that it sometimes executes twice, i.e. a second time when the first execution is still running, so concurrently causing all kinds of problems.

The monitoring screen in data factory is more or less providing evidence of the second execution:

User's image As you can see the execution duration of 1606 seconds (~27 minutes) is not in line with the 39 minutes reported duration. But this completely corresponds to our own logging where we can see that the first execution lasted about 39 min and the second execution about 27 minutes, until both ended up in an error.

Am I doing something wrong or is this really Azure Data Factory failing?

Below is the code of the stored procedure pipeline activity:

    "name": "Load Stream 3",
    "type": "SqlServerStoredProcedure",
    "dependsOn": [],
    "policy": {
        "timeout": "0.12:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "storedProcedureName": "[dbo].[usp_LoadStream]",
        }
    },
    "linkedServiceName": {
        "referenceName": "myLinkedService",
        "type": "LinkedServiceReference"
    }
}

Harishga 5,990 Reputation points Microsoft Vendor

2024-06-27T07:47:36.2833333+00:00

Hi @Matthias Delafontaine
Welcome to Microsoft Q&A platform and thanks for posting your question here.

Your Azure Data Factory pipeline is running a stored procedure more than once, even though you've set retries to 0. This is unexpected behavior and not what you would expect from the configuration you've provided. There are a few possible reasons why this is happening.

One reason could be that there are multiple triggers for the pipeline that are overlapping, causing the pipeline to run again before the first execution is complete. Another reason could be that there's an issue with the pipeline configuration that's not visible in the code you've shared. For example, there might be other activities in the pipeline that are triggering the stored procedure again. In rare cases, there might even be a glitch in the ADF service itself that's causing the pipeline to run twice.

To fix the issue, you can try reviewing the triggers associated with the pipeline to make sure they're not set up to cause multiple runs. You can also review all activities in the pipeline to ensure there isn't another activity causing the stored procedure to execute again. Additionally, you can use ADF's monitoring features to track each activity run and look for patterns or discrepancies. You can also enhance logging within your stored procedure to capture start and end times, as well as concurrent executions. This can help identify when the second execution starts.

I hope this information helps you. Let me know if you have any further questions or concerns.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Harishga 5,990 Reputation points Microsoft Vendor

2024-06-28T05:33:35.6366667+00:00

Hi @Matthias Delafontaine
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Matthias Delafontaine 16 Reputation points

2024-06-28T07:14:43.27+00:00

Hi Harishga,

This stored procedure is only called from within that pipeline and that pipeline was the only pipeline running at that time. In other words, I have no choice but to conclude that this is, as you call it, a glitch in the ADF service calling the procedure twice.

In addition, I yet provided you some evidence for that: how do you explain the big discrepancy between 26m46s = 1606 seconds and 38m52s = 2332 seconds ? You didn't provide me any feedback on that. If I look at the other 2 load stream calls in the pipeline that did went successful, I see only a discrepancy of a couple of seconds (e.g. 10m24s = 624 seconds vs 10m27s = 627 seconds).

I have also another example of a failed one that was executed twice with again a very big discrepancy there, exactly coinciding with the time we see the double execution starting in the database. So how would you explain these findings?
Harishga 5,990 Reputation points Microsoft Vendor

2024-06-28T09:45:02.08+00:00

Hi @Matthias Delafontaine
There are a few potential explanations for this issue. One possibility is double execution, where the procedure might be called twice due to a glitch in the ADF service, leading to longer execution times and discrepancies. Another factor could be resource contention, where multiple processes are competing for the same resources, causing delays and inconsistencies in execution times. Network latency might also play a role, as variations in network performance can lead to significant differences in execution times, especially if data transfer is involved. Additionally, pipeline configuration should be checked to see if there are any retry policies or parallel executions configured that might cause the procedure to run multiple times.

To further diagnose the issue, you need to review the ADF pipeline logs for any anomalies. Checking the database logs can help determine if the stored procedure is indeed being called multiple times. It’s also important to ensure there are no overlapping schedules or triggers causing unintended executions.

I hope this information helps you. Let me know if you have any further questions or concerns.
Harishga 5,990 Reputation points Microsoft Vendor

2024-07-01T03:54:13.1433333+00:00

@Matthias Delafontaine
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Azure Data Factory executes stored procedure twice (and concurrently)

Your answer