0

I have a simple logic app as shown below. When I run this and check the SQL connections (sp_who2) I can see 'sleeping' connections being created. By running enough times the number of sleeping connections reaches 300 and the logic app then fails.

Its as if the logic app is not freeing up connections on each run of the procedure. Any help much appreciated. Also the logic app does not just create 1 SQL connection per procedure execute it seems to create approx 5.

If you recheck the database after sometime I can then see SQL remove the sleeping connections and make them available.

Logic App Test

Nick Fox
  • 1
  • Can you tell us what error the logic app gives when it fails? – majita Jan 13 '20 at 14:08
  • Hey there: Resource ID : 2. The session limit for the database is 300 and has been reached – Nick Fox Jan 13 '20 at 17:01
  • Hi Nick, is it ok if set your logic just run one instance at the same time(I'm not sure if it will match your requirements after doing this setting) ? If it still matches your requirements, you can try to click the "..." button at the upper right corner of "When a HTTP request is received" trigger and enable the "Concurrency Control" and then set the "Degree of Parallelism" as 1. – Hury Shen Jan 14 '20 at 01:46
  • Sql server sometime uses more than one worker thread to execute a query so the number of connections doesn't always correspond to the number of execution threads - from: https://stackoverflow.com/a/37919964/2401021 might be a good explanation of why you see multiple connections per procedure execution – majita Jan 14 '20 at 06:57
  • Thanks both - unfortunately even after setting Concurrency Control to 1 it still does it. I can see the sessions spinning up into the 100s – Nick Fox Jan 14 '20 at 07:14
  • I've just had a breakthrough - if I switch the SQL API connection to use SQL authentication rather than Azure AD Integration the issue goes away – Nick Fox Jan 14 '20 at 07:20
  • So your problem was solved, right ? – Hury Shen Jan 14 '20 at 09:22
  • By adjusting the Authentication from AD integration to SQL authentication fixes the issue. I don't really want to do this though, any ideas? – Nick Fox Jan 14 '20 at 12:24
  • Hi @NickFox, Congratulations that the issue can be fixed by using SQL Authentication. I just help you post it as answer. At least it's a solution. You can think about mark it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. . Thank you. If anyone have good ideas about AD integration, I think he will share to you. You still can waiting for it. – Leon Yue Jan 22 '20 at 03:01

1 Answers1

0

Congratulations you find a way(not the best way you want) to fix the issue:

"By adjusting the Authentication from AD integration to SQL authentication fixes the issue."

I post it as answer and this can be beneficial to other community members.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23