question

jasejackson-8080 avatar image
0 Votes"
jasejackson-8080 asked jasejackson-8080 commented

Azure SQL Database - Transient Errors

We have a relatively simple scenario where Logic App (la) executes a azure sql database stored procedure (which executes a number of sub procedures). However we have an issue where the store procedure ends without error which I assume is related to "transient errors'. I've assumed this whilst there are also "time out" logs at the same time as the "disconnection". The procedure has successfully executed for years. The point at which it ends differs each time and is not at a stage which is resource heavy/intensive.

1) we have logging for errors/time outs but there is no log of a disconnection "drop out"

2) Is there a best practice approach to manage such a scenario. Clearly the connection has been lost so the stored procedure cannot handle the issue. Assume we could use the logic app to check after a period of time and if a condition is not met try again? Just wanted to check if there is an advised/official more robust approach to handle the scenario.

We have not used ADF for the processing of the stored procedures due to the cost as it has to process and wait for a significant number of procedures to execute (the process can take a number of hours to complete)

Thanks

azure-sql-databaseazure-logic-apps
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @jasejackson-8080 , welcome to Microsoft Q&A forum.

Would it be possible for you to share the logs of time out and disconnection by masking the sensitive information?

0 Votes 0 ·

Below are the time out logs at time of disconnection.

77164-image.png


0 Votes 0 ·
image.png (6.9 KiB)

The avg log write percentage is also high (97% - 100%) when manipulating this table. A number of columns are dropped and then added with default value contraints added; the constraints are then dropped.

Until recently this process lasted a couple of seconds whereas now it takes 5+ minutes and the table has not grown in size significantly.

0 Votes 0 ·
Show more comments

1 Answer

jasejackson-8080 avatar image
0 Votes"
jasejackson-8080 answered jasejackson-8080 commented

We have rebuilt the table and the performance is now 1 second again. I assume this performance degradation was caused by fragmentation of the table? I'll close the question.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is the table a heap? I. E., a table without a clustered index? Sql server can leave lots and lots of free space in heaps, depending on the modification pattern. Either make it a non-heap table, or try to pin point what in your modification that causes this extremely sparse table. If that was the root of your problem, that is. Else you are just waiting for this to happen again.

0 Votes 0 ·

The table is indeed a heap (no clustered index). The table has a relatively high number of inserts/deletes and also drop/add column actions. So these actions I assume cause the issue. I'll investigate whether adding a clustered index is feasible. Thanks

0 Votes 0 ·