Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

Question

I am working on an application using WebApi and AngularJS. I am getting this exception after spending sometime to application. I am using EntityFramework in this app.

"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 your database connections (it's 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 time out for querys. Check too if you have long time executing querys.

Perhaps, duplicate question:

How can I solve a connection pool problem between ASP.NET and SQL Server?

When does Entity Framework open and close Database Connections?

Answer 2

Please try the following things

1. Always close your connection in the finally block

2. Increase pool size like in your connection string

   string connectionString = "Data Source=localhost; Initial Catalog=Northwind;Integrated Security=SSPI; Min Pool Size=10; Max Pool Size=100";

or

3. Don't use pooling at all

   string connectionString = "Data Source=localhost; Initial Catalog=Northwind;Integrated Security=SSPI; Pooling=false;";

Answer 3

I just experienced the same problem. I ended up using a pattern like this which seemed to fix the issue:

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 it. It seems like MS should change their recommendation since I've found it all over their site suggesting not to use the try { } finally { con.Close(); } pattern. I didn't try this explicitly, since the pattern is fairly pervasive throughout our entire db layer and wanted to find something closer.

I hope this helps someone.

Answer 4

It was suggested to use the using statement around SqlConnection and SqlCommand objects.

Please note that if you have a function returning an IEnumerable with the use of yield return in a SqlDataReader loop, this is not the recommended pattern. Doing so, the connection to the database will be closed before the data reader will be executed.

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

Answer 5

a bit old, sorry for diggin this out, but it happened to us this week at work :

either as @sohail naseer answered, your connections aren't closed, or you have a data class that isnt 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'll have created 105 connection (thus busting your 100 max allowed ) even if you close and dispose the objects correctly, SQL still need time to reassigned that connection to a new user.

Answer 6

Force garbage collector call:

System.GC.Collect()