Solved

Open SQL connections for all scheduled flows

  • 27 September 2022
  • 6 replies
  • 99 views
AndreKemmeren
Rank
Userlevel 1

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.

icon

Best answer by Dick van den Brink 4 October 2022, 11:36

View original
This topic has been closed for comments

6 replies

D
Rank
Userlevel 4
Badge +2

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
Rank
Userlevel 1

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
Rank
Userlevel 4
Badge +2

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
Userlevel 4
Badge +5

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

 

 

 

AndreKemmeren
Rank
Userlevel 1

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

Kasper Reijnders
Userlevel 4
Badge +5

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

Nice! Hopefully it does not return. 

Terms & ConditionsCookie settings