Max Pool Size Was Reached!

Hello ,
When dealing with ADO.Net , You might receive the following 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.

Now, I’m going to elaborate on this issue:
1) This exception may occur if you open 100 connections to database and do not Close or Dispose them, For producing this exception, you may write a loop from 1 to 100 and open a connection to database each time and leave it open, You will get the mentioned exception.
2) Another possible reason is that When SQL Server Debugging is turned on, you open and close connections quickly and this causes the exception. For getting information on how to work around this, you may visit http://support.microsoft.com/kb/830118
Note: This Applies to Visual Studio.Net 2003.
3) Another point is that when using SqlConnection , You do not Close or Dispose it and leave it open , Lets get into this reason with an example :

  SqlConnection sqlConn = new SqlConnection("Data Source=DotNetPC;Initial Catalog=DppcDB;Integrated Security=True");
  sqlConn.Open();
  SqlCommand sqlCmd = new SqlCommand();
  sqlCmd.Connection = sqlConn;
  sqlCmd.CommandType = CommandType.Text;
  sqlCmd.CommandText = "select * from Management.Logs";
  sqlCmd.ExecuteNonQuery(); // Connection will remain open

above code is not acceptable and may lead to the exception , You may use one of the following code styles:
1) Implementing ‘using’ keyword as shown below:

using (SqlConnection sqlConn = new SqlConnection("Data Source=DotNetPC;Initial Catalog=DppcDB;Integrated Security=True"))
{
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "select * from Management.Logs";
sqlCmd.ExecuteNonQuery();
} // It ensures that connection will close & dispose

2) Putting Try—Catch—Finally Block into work :

SqlConnection sqlConn = new SqlConnection("Data Source=DotNetPC;Initial Catalog=DppcDB;Integrated Security=True");
try
{
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "select * from Management.Logs";
sqlCmd.ExecuteNonQuery();
}
catch (Exception)
{
//Do Something
}
finally
{
//'finally' block is ALWAYS get called.
sqlConn.Close();
sqlConn.Dispose();
}

What is the different between Close and Dispose?
The only difference is that ‘Dispose’ method clears the connection string and pool group and then calls the ‘Close’ method.
I’ve used Reflector to prove this:

public override void Close()
{
IntPtr ptr;
Bid.ScopeEnter(out ptr, " %d#", this.ObjectID);
try
{
SqlStatistics statistics = null;
RuntimeHelpers.PrepareConstrainedRegions();
try
{
statistics = SqlStatistics.StartTimer(this.Statistics);
lock (this.InnerConnection)
{
this.InnerConnection.CloseConnection(this, this.ConnectionFactory);
}
if (this.Statistics != null)
{
ADP.TimerCurrent(out this._statistics._closeTimestamp);
}
}
catch (OutOfMemoryException exception3)
{
this.Abort(exception3);
throw;
}
catch (StackOverflowException exception2)
{
this.Abort(exception2);
throw;
}
catch (ThreadAbortException exception)
{
this.Abort(exception);
throw;
}
finally
{
SqlStatistics.StopTimer(statistics);
}
}
finally
{
SqlDebugContext context = this._sdc;
this._sdc = null;
Bid.ScopeLeave(ref ptr);
if (context != null)
{
context.Dispose();
}
}
}

protected override void Dispose(bool disposing)
{
if (disposing)
{
//Clears the connection string and pool group.
this._userConnectionOptions = null;
this._poolGroup = null;
//Calls the close method.
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}

Of course , you may increase the Connect Timeout or Max Pool Size in connection string , but you are advised to looking for problem at first.I’m
Looking forward to your extra information.

Advertisements

One Response to Max Pool Size Was Reached!

  1. obalit says:

    Thanks ,Good Idea.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: