Logic App > Execute Stored Procedure execution time, Scheduled vs. Live Execution

Jason Zech 0 Reputation points
2025-01-29T21:40:49.6033333+00:00

I have created a simple logic app that executes a stored procedure within an Azure SQL database in the same resource group. It's a fairly large data processing job, but not extraordinary. When I execute the stored procedure directly in the SQL server, it takes about 2 minutes to complete. When I manually run the logic app, the workflow also completes the job in about 2 minutes.

I need this stored procedure to run once per day, so I set up a scheduled trigger. However, the run times on the logic app are absurdly long -- pushing 2 hours at this point, and once or twice a week I get a Gateway Time out error.

Besides the manual vs. scheduled trigger, there is no difference. Why is this happening? What can I do to reliably run this stored procedure without manual intervention?

Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
3,336 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Ranashekar Guda 10 Reputation points Microsoft Vendor
    2025-01-30T07:33:26.5433333+00:00

    Hi @jason zech ,

    Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

    The delay you're encountering with the logic app executing the stored procedure on a schedule, as opposed to running it manually, may be due to the timeout restrictions set by the SQL connector in Azure Logic Apps. The SQL connector enforces a stored procedure timeout limit of under 2 minutes, which can result in 504 Gateway Timeout errors if the stored procedure exceeds this duration.

    • When executed manually, the process may finish within the timeout limit; however, when run on a schedule, there may be extra overhead or resource contention that impacts performance, resulting in longer execution times. To mitigate this issue, you might consider the following strategies:
    • Rather than directly invoking the stored procedure from the logic app, you can configure an Azure Elastic Job Agent to execute the stored procedure asynchronously. This approach enables you to manage long-running processes more efficiently.
    • Establish a state table to store parameters and monitor the execution of the stored procedure. This will assist in managing the execution state and help prevent timeouts and if you utilize a job agent, you can adjust the job's timeout settings to allow for longer execution durations. By default, a job step times out after 12 hours, but this can be modified to meet your requirements.
    • At last, examine the stored procedure for possible optimizations that may help decrease execution time.

    By applying these strategies, you should be able to execute your stored procedure consistently without the need for manual intervention.

    Also, for your better understanding, please refer to this document regarding Timeout limit on stored procedure execution.

    I hope this helps! Let me know if you have any further questions or need additional assistance.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.