Indications that we are leaking connections

http://blogs.msdn.com/b/bindeshv/archive/2008/03/02/indications-that-we-are-leaking-connections.aspx

We get a lot of queries from developers around the following error while they are using ADO.NET

"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."

Most of the times this is an indication that we are leaking connection. Yes, but you would state that how can this be possible since we are explicitly closing the connection in the code. I do agree, but what happens if the program follows a different code path. Here is a link where the issue is explained in depth

http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx

By default, the max pool size is set to 100. This is more than sufficient unless your program opens 101 connections inside a single thread, which is a rare scenario.

Most of the times you are lucky since the GC will try to recover the unused SqlConnection object, and so you might not get this error at all. And so if you right a sample application to test the error, that opens 101 connections and does not close it , you will not see the error, since chances are that GC would have reclaimed the open connection.

How do we identify if we have a connection leak ?

Simplest way to identify a connection leak is to run the Performance Monitor utility and add the .Net Data Provider for SQL Server for the given process that you suspect leaks the connection. Please remember that you need to attach it to the right application process else you will not have correct information.

Once attached look for the NumberOfReclaimedConnections. This counter is a newly added counter for ADO.NET 2.0 . Any number here will tell the story.