Figuring Out Connection Pool Errors

October 1st, 2012
By: Cory Mathews

This is the error I had to go on.

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.

It sounds pretty obvious that it is a database connection that was not being closed. But I have hundreds of points of connection in my webapp. How do I figure out which one I am forgetting to close?

First exec

sp_who2 

on sql server and look at all those connections, now find a bunch connected to your database with the status “sleeping” and the command “AWAITING COMMAND”. They also give you the SPID which comes in very handy.

Take the SPID and run it in dbcc inputbuffer()

dbcc inputbuffer(SPID)

This will tell you the command that was last run by that process id, then all you have to do is find that query or stored procedure in your apps code and make sure you close the connection.

Deeper Problems

This can be a symptom of a larger problem with your database connection class. You should not have to manually close a connection every time you connect.

SqlConnection uses the iDisposable interface so wrap your connection statement in a using statement. When a SqlConnection is disposed it is also closed so simplify and let .net take care of it for you.

using (SqlConnection connection = new SqlConnection(connectionString))
{
....
}