How to find what is using the connections in my connection pool

Question

I have a problem in the code that I have written using .NET.

The problem is that somewhere I have some dodgy database code that means that after some time I get the following error:

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.

I know that it is because somewhere I haven't disposed of one of my datareaders or something similar which means it still has the connection open so its not being returned to the pool. I'm having a bit of problem finding where this is happening in my code though.

So my question:

Is there any way to query the connection pool to find out what its in use connections are doing. I'm just looking for a way to find what query is being run to allow me to find the offending piece of code.

For what its worth I don't have permissions to run an activity monitor on the database in question to find out that way.

Answer 1

Is there any way to query the connection pool to find out what its in use connections are doing.

No. Not really. The connection pool is something that your application maintains (actually a List<DbConnectionInternal> ) If you really wanted to you could get to the connections in the pool via reflection or if you are debugging, via a local or watch window (see below), but you can't get to what's happening on that connection from there or which object should have called Connection.Close (or Dispose). So that doesn't help

If you're lucky you can execute sp_who or sp_who2 at the moment you get the timeout when you've run out of pooled connections when but its highly likely that most of the results are going to look like this .

SPID Staus    Login Hostname  Blkby DBname Command          ....
---- -------  ----- --------- ----- ------ ---------------- 
79   sleeping uName WebServer .     YourDb AWAITING COMMAND .....
80   sleeping uName WebServer .     YourDb AWAITING COMMAND .....
81   sleeping uName WebServer .     YourDb AWAITING COMMAND .....
82   sleeping uName WebServer .     YourDb AWAITING COMMAND .....

Which means that yes indeed your application has opened a lot of connection and didn't close them and isn't even doing anything with them.

The best way to combat this is to profile your application use the ADO.NET Performance Counters and keep a close eye on NumberOfReclaimedConnections and also do a thorough code review.

If you're really desperate you can Clear the pool when you encounter this problem.

using (SqlConnection cnn = new SqlConnection("YourCnnString"))
{

     try
     {
            cnn.Open();
     }
     catch (InvalidOperationException)
     {
             SqlConnection.ClearPool(cnn);
     }
     cnn.Open();

}

I do however caution you against this because it has the potential of choking your DB server because it allows your application to open as many connections as the server will allow before it just runs out of resources.