Gateway time out after 2 mins in Azure Logic apps while running stored procedure on Azure SQL DB

mo boy 396

Dear Experts,

I am trying to perform rebuild indexes on an Azure SQL DB using Logic apps. I used the SQL Server (Execute stored proc V2) for this purpose. It appears to run for the first two minutes and then times out after that. On searching online, I came across some suggestions around pagination etc. (Link)

Is there any easy way by which we can make the stored procedure persist with the connection to the Azure SQL DB.

Could you please share your inputs?

Anurag Sharma 17,576 Reputation points

2020-09-14T09:41:15.37+00:00

Hi @mo boy , thanks for reaching out to us. Azure logic Apps have the timeout defined as 120 seconds for inbound and outbound requests. For operations that run longer, there is a way to use asynchronous calling and create a new thread on which this operation runs. Please refer to below links for the details.
https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-limits-and-config#http-limits
https://github.com/logicappsio/LogicAppsAsyncResponseSample

However, the article you mentioned is also a good way where in we can pass a parameter to Stored procedure to run it for a fixed number of objects. Then we can loop through the stored procedure until indexes for all the tables have been built. This looks like an easy way and also comply with 120 seconds of timeout. If possible, could you share the stored procedure text and we will look into parameterizing it and call through logic apps?
mo boy 396 Reputation points

2020-09-14T22:29:20.967+00:00

Thank you for the reply @AnuragSharma-MSFT

Please refer this stored procedure. This will run at least for an hour.

Create Procedure RebuildIndexes
as
ALTER INDEX [nci_Status_Id] ON [dbo].[Queue] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [PK_dbo.Queue] ON [dbo].[Queue] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [PK_dbo.TransID] ON [dbo].[TransID] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [IX_Queue_Id] ON [dbo].[TransID] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [nci_code_time] ON [dbo].[TransID] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [PK_dbo.Details] ON [dbo].[Details] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [nci_wi_Queue_D] ON [dbo].[Queue] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [IX_Id] ON [dbo].[Queue] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [PK_dbo.TransactionID] ON [dbo].[TransationSummary] REBUILD WITH(ONLINE=ON,MAXDOP=1);
ALTER INDEX [PK_dbo.DetailsID] ON [dbo].[DetailsSummary] REBUILD WITH(ONLINE=ON,MAXDOP=1);
Anurag Sharma 17,576 Reputation points

2020-09-15T10:36:31.617+00:00

Hi @mo boy , from the script of stored procedure, it looks any of these can take more than 2 minutes to execute, hence looping through the SP and execute indexes rebuild would not be suited for this approach.

However, I was checking around on creating a Azure Function that would trigger the stored procedure and can run for longer time until the execution is completely done. Link mentioned below:
https://serversncode.com/azure-functions-with-sql/

Just wanted to check once again if we could use azure function instead of Azure Logic app. If not, there is way to integrate the azure function still in Azure Logic App through HTTP Webhook, we can try to utilize that too.
https://medium.com/@jeffhollan/calling-long-running-functions-from-logic-apps-6d7ba5044701

Please let us know you preferred approach and would it be possible to use above solution for the same.
mo boy 396 Reputation points

2020-09-17T08:51:51.537+00:00

Thank you @AnuragSharma-MSFT

I am not quite familiar with Azure functions, I need to read through to get a better understanding.
On searching earlier, it appears there is a limit of 10 minute time out in Azure functions, I am not sure though.

My only peeve is the 2 minute timeout limit in Azure logic app. Otherwise, I thought Logic app is really user friendly to use.

Thanks again for your time, much appreciated.