Timed out. The wait period expires before a connection is received from the pool.

Question

I am working on an application using WebApi and AngularJS . I get this exception by spending some time on the application. I am using EntityFramework in this application.

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

Stack trace

 at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) ↵ at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(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 System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) 

Answer 1

Close the database connections (this is really important).

 SqlConnection myConnection = new SqlConnection(ConnectionString); try { conn.Open(); someCall (myConnection); } finally { myConnection.Close(); } 

or

 using (SqlConnection myConnection = new SqlConnection(ConnectionString)) { myConnection.Open(); someCall(myConnection); } 

Check how many users are connected to your database and the timeout for queries. Check also if you run queries for a long time.

Perhaps a recurring question:

How to solve connection pool problem between ASP.NET and SQL Server?

Answer 2

Please try the following:

• Always close the connection in the finally block

• Increase the pool size, as in the connection string string connectionString = "Data source = localhost; Start directory = Northwind;" + "Integrated Security = SSPI; Minimum Pool Size = 10; Maximum Pool Size = 100";

    or 

• Do not use pools at all string connectionString = "Data source = localhost; Start directory = Northwind;" + "Integrated Security = SSPI; Pooling = false;"

Answer 3

I just experienced the same problem. I ended up using a template that seems to fix the problem:

 using (SqlConnection con = new SqlConnection(strCon)) { using (SqlCommand cmd = new SqlCommand(strCmdText, con)) { con.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { //do stuff; dr.Close(); } } con.Close(); } 

This seemed to fix my problem. DataReader.Close() was the nail that did this. It seems that MS should change its recommendation since I found it on my website, suggesting not to use the try { } finally { con.Close(); } try { } finally { con.Close(); } . I have not tried this explicitly since the pattern is quite common throughout our db layer and wanted to find something closer.

I hope this helps someone.

Answer 4

The garbage collector call:

 System.GC.Collect() 

Answer 5

a little old, sorry for that, but it happened to us this week at work:

either in response to @sohail naseer, your connections are not closed, or you have a data class that is not used correctly:

If you loop 105 times, and in each loop you declare a new data object and you query the DB with it, you would create 105 connections (this way you can allow your 100 max), even if you close and delete objects correctly, SQL it still takes time to reassign this connection to a new user.

Answer 6

It has been proposed to use the using statement around SqlConnection and SqlCommand objects.

Note that if you have a function that returns IEnumerable using a return return in a SqlDataReader loop, this is not a recommended pattern. In this case, the database connection will be closed before the data reader is executed.

Instead, apply the CommandBehavior.CloseConnection parameter to the ExecuteReader call.