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.