Skip to main content
Solved

Open SQL connections for all scheduled flows

  • September 27, 2022
  • 6 replies
  • 114 views
AndreKemmeren
Captain
Forum|alt.badge.img+3

We are getting errors on our Indicium when executing process flows, it seems that Indicium opens a SQL connection for every scheduled process flow.

On paper this sounds not to bad but our development environment we run into the limitations of Azure SQL. Azure SQL only has a limited amount of open connections which we are running into on our current tier (which is for all other metrics plenty). Currently it results in SQL errors regarding to many open connections.

It also seems a bit strange to have constant open connections even for process flows that might only run once per day, week or even less.

Best answer by Dick van den Brink

Can you create a TCP ticket for this with the error log (including stacktrace) and also the output of sp_who2?
Hopefully it can give us some insights into why connections are hanging (or why they are not reused like they are supposed to).

View original
AndreKemmeren
1 person likes this
  • AndreKemmeren
    AndreKemmeren
Did this topic help you find an answer to your question?
This topic has been closed for comments

6 replies

D
Moderator
Forum|alt.badge.img+3

Hi Andre,

Indicium does not create a connection for every (system) process flow, but it (re)uses one from the pool of connections. If none are available a new one is created.

Is it possible to provide us the error? 
I am curious to know which database is running into connection issues, because system flows are scheduled by the IAM database, but most of the standard system flows are connecting to the Software Factory database.
Or is this issue about system flows created for your end application?

 

Also, can you provide some information about your Azure SQL database? What service tier and Compute tier it is using?
 

AndreKemmeren
Captain
Forum|alt.badge.img+3
  • AndreKemmerenCaptain
  • Author
  • Captain
  • 36 replies
  • September 27, 2022

It is the IAM database. 

The error is 100% related to to many connections, we see this error very regularly in the logs:

2022-09-26T18:14:35.5493416+00:00  [ERR] Error scheduling system flow 'system_flow_connect_to_db' for application 36. (4aa0e40e)
Microsoft.Data.SqlClient.SqlException (0x80131904): Resource ID : 1. The request limit for the database is 90 and has been reached. See 'https://docs.microsoft.com/azure/azure-sql/database/resource-limits-logical-server' for assistance.

 

When I look at sp_who2 I see atleast 50 open(idle) connections when nothing is being done on the platform.

 

We are using the Standard tier, currently on S1 for IAM.

D
Moderator
Forum|alt.badge.img+3

Can you create a TCP ticket for this with the error log (including stacktrace) and also the output of sp_who2?
Hopefully it can give us some insights into why connections are hanging (or why they are not reused like they are supposed to).

Kasper Reijnders
Forum|alt.badge.img+5

I am running into a same issue, did the investigation here result into some finding/solution?

 

 

 

AndreKemmeren
Captain
Forum|alt.badge.img+3
  • AndreKemmerenCaptain
  • Author
  • Captain
  • 36 replies
  • November 22, 2022

Can't help you, the problem disappeared for us.

Kasper Reijnders
Forum|alt.badge.img+5
AndreKemmeren wrote:

Can't help you, the problem disappeared for us.

Nice! Hopefully it does not return. 

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings