This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(208, 57.99999999999999%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJJ%3C/text%3E%3C/svg%3E)
Hi
We have a number of long running SProcs (all have separate SProcs/queries within). The longest approx 50 mins. These are individually executed from logic apps and this way were do not hit the 2 hour azure SQL database timeout issue.
This worked successfully until recently when one of the SProcs simply ends part way through; at or v close to the same step in the SProc. The tables in the very simple Update query (approx 70k records updated) have been rebuilt, indexed and the stats updated but still failure. This is approx 38 mins into the SProc.
Other longer SPriocs work with success. Also at the time of the "drop out" a timeout log is created but this doesn't appear to offer much insight. The overall job has been ran at different times of the day and still fails at this point. When the SProc is executed manually it completes with success.
Can you advise what else we should be checking or validating.
Have you identified what is the peace of SQL programming where the timeout comes up? Could you please share with us that piece of code? are you able yo run apart that piece of code and creates a query plan you can share with us?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(25.6, 10%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
@jase jackson USA Can you please post the error message for us to troubleshoot?
Table A is the table being updated.
I do not believe the query is the actual cause of the issue. Although the job fails here 95% of the time it has also failed before this step of the proc or just after. This query and the queries immediately after or before take less than a second to process (updating 50k records on P4 level). Another important point was this was processing successfully for 6 months beforehand.
Leading up to this process there are stages that insert 2 million rows into a table and then update a large number of columns.
The Time Out log has entries with query_hash:-1. Is there a sql limitation and the connection is ended
Manually running the procedure is always successful. The overall parent Store Procedure is executed from a Logic App. Failure/loss of connection occurs around 35+ minutes. Other logic App executions process procedures that run for 60 minutes with success.
When performing bulk operations (millions), updates and inserts on tables involving non clustered indexes, it is recommended that you drop the index first, perform the DML operations and then create the index again.
Please test this approach if it is feasible in your scenario.
I think my question is actually this...why when we run a series of procs in sequence that run for 3+ hours they complete successfully via smss however when a logic app executes the same procs via runbooks they fail typically at the same points each time. These same points of failure are not necessarily based on time but the the stage/step. Almost as though this approach is more sensitive to database statistics/indexes?
Re logic apps - What is odd is a stage that may take an hour completes however another which takes half that always fails admittedly after a period of high i/o.
Is there another approach to execute store procedures that run for a number of hours. I know ADF is an approach but this appears to be costly; satisfactory for processing data when completed in minutes but expensive when processing takes a number of hours.
Moved the long running SProcs to ADF and this has proved successful with relatively small increase in financial cost. Not sure I'll find out what using Runbooks did not work successfully as no other changes have been made.
The job is now stable and overall is slightly quicker in execution so off sets the increase in ADF cost in reduced SQL Dbase cost.