Micro Focus Contributor
Micro Focus Contributor
472 views

(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.

 

0 Likes
1 Reply
Regular Contributor.. mtthwcruz Regular Contributor..
Regular Contributor..

Re: (OO) Support Tip: MSSQL query failed

@zhanqiangyan Where exactly should I implement this? Should I add it to the central database.properties OO config file?

Thanks in advance.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.