C# Connection pool issue: The timeout period elapsed prior to obtaining a connection from the pool-CodePudding

OrmLiteConnectionFactory is responsible for creating a new SqlConnection. MaxConnectionPoolSize = 100. PoolSize < 50. Sometimes while opening a new connection SqlClient throws:

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.

Call Stack:

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.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at ServiceStack.OrmLite.OrmLiteConnection.Open()

So, the max connection pool size is not reached but a new connection can't be established. Really appreciate any thoughts.

CodePudding user response：

The connection pool is machine wide. Are there other apps or processes running that use database connections? Maybe your SQL Server has a 'max worker threads' limit?

Otherwise an answer is to look at why so many connections are in use for so long. Maybe the connections can be released earlier.

CodePudding user response：

What you must do every time your app does db work is to make sure that SqlDataReaders, SqlCommand, SqlConnection objects are closed and disposed of as soon as possible. The easiest way to do that is to use the Using keyword on objects that implement IDisposable. Otherwise, these types of objects remain in memory until the garbage collector gets around to destroying them. Check source code in your entire app to implement the above method.

I had to do this when the exact same issue reared its head when my company's customers were calling our APIs!