Hi all,
I'm running into an issue with a web application that is exhausting all available connections in the connection pool. I seem to recall some good tools used to diagnose all active connections, but am drawing a blank. What are some good options for diagnosing this issue?
Are you using SqlDataReader objects in your application? A SqlDataReader object will keep its connection open until it is closed.
I vote for a code review. I once had a similar situation with an app built by third parties, and doing a search for connection quickly exposed it didn't do using/finally to make sure connections were closed when an exception occured.
Also as pointed out by Michael, you have to make sure connections used by data readers are closed. You can specify the connection is closed when the reader is closed, then for those cases just make sure the data reader is disposed (with an using(){} )
You can run sp_who to see active connections from the SQL server, but, it's likely you aren't closing out your connections as other have suggested. However, if you are closing the connections, but reopening them immediately (like in a loop reading a text file or something), you can use them all even though you think you are closing them (look at Connection Pooling). If you post an example of your code, I think you'll get a better answer.
.Net memory profiler of WinDbg with SOS will allow you to track down who is holding references to your connection objects, with that information you should be able to track down the offending methods.