(OO) Support Tip: MSSQL query failed

Environment:

OO 10.6

Current Issue:

we have configured in Production two jobs which are checking amount of records (GLC and eventout) in database. These jobs are scheduled in HP Operations Orchestration. For both jobs on MSSQL query step we are getting errors:


"(count=Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.)"
(exception=System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at dotNETWebActions_SQLQuery.SQLQuery.Execute(ActionRequest aReq, ISession aSession, IActionRegistry aReg)), (returnCode=1), (returnResult=Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.), (sessionId=new session), (isSessionOn=False), (Result=Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.)

We investigate Database/network side but no issues found. Issue is fixed after HPOO RAS services restart. But it is workaround only for couple of days.

Troubleshooting done:

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

Solution:

increase sqlserver.connection.maxpoolsize value
number of max connections
sqlserver.connection.maxpoolsize=200

Confirmed the SQL query operation from Databases CP

##############################################################
 For the flows under Library/Operatons/Databases/Microsoft SQL
 The following configuration is for .NET connection pooling.
 ###############################################################
#
 Maximum number of connections for SQL Server.
#
sqlserver.connection.maxpoolsize=100
#
 Connection lifetime in seconds for SQL Server.
#
sqlserver.connection.lifetime=3600

But customer said issue still remains.

So new solution provided:

#
 sql server connection pooling configuration
#
number of max connections
sqlserver.connection.maxpoolsize=200

connection lifetime in seconds
sqlserver.connection.lifetime=7200

After this change, issue resolved.

Hope the above helps.