If you get the following error:

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.

Make sure that you are closing your connection. Specifically closing connections that you opened transactions from.

It seems the Connection Pool implemented in ADO.NET has two lists. One for connections that you did not open transaction from and the other for connections that a transaction was opened (and did not close, yet).

The two lists share the same amount of connections set in the “Max Pool Size” parameter passed in the connection string (the default is 100).

Be sure to use one of the following patterns and make sure to call connection.Close() when you are done with running your SQL statements, otherwise, it will take until the next run of the Finalizer thread to call Dispose on your connection object which will return it to the pool.

If your application is loaded it might take quite a while until the Finalizer thread runs and by then you will exhaust your connection pool and start getting mentioned above.

The two patterns used in this scenario are:

using (IDbConnection = new SqlConnection()) {

DoSomething();

}

The “using” statement is only good for C# and it will call Dispose (which in turn will call Close) on your connection object.

Another pattern which is similar and gives you more control is this:

IDbConnection = new SqlConnection();

try {

DoSomething();

}

finally {

connection.Close();

}

The finally block gives you better control and can also be combined with a “Catch” block that you can gracefully handle various errors such as “Unique Key Violation” (if you application logic needs this kind of treatment).

If you really want to know that you have exhausted your connection pool and you forgot to run PerfMon (see my previous post about PerfMon) you can take a memory dump using adplus (you can read a little bit about adplus here) and use WinDbg and the SOS extension to look at the Connection Pool object.

Search for “ConnectionPool” and you’ll get various objects, one of them will be “DbConnectionPoolGroup” which has a property named “_poolCount” which can tell you the count of connections used on your pool. This will give you a rough estimate as to what is the status of your connection pool.

 

  • Michael

    The “Using” statement is also available in VB.Net (VS2005).

  • Lilymartinez2009

    Thanks Michael. I was wondering