Detect non-closed connections to SQL

I've inherited a very large project in ASP.net, SQL 2005 and have found where some SQL connections are not closed - which is bad. Without going thru every line of code, is there a way 开发者_StackOverflowto detect if connections are not being closed? Performance counter? as a follow up - how does SQL reclaim unclosed connections. I'm using non-pooled connectionstring.

• Sql does not reclaim them. They are open until force closed by admin or - well - from the client side.
• Which would be garbace collecting the connection objects.

Possibly a memory profiler is your best bet - try finding out where the (lost and non-disposed) connections come from.

Looks like really crappy code to me - because in all asp.net apps I have ever written, connections where handled in one point in the code only. Seems someone spread that all over the place here.

One hint may be: look at the SQL from those stale connections. You should be able to retrieve the last sql statement, which may give you a hint where they originate.

I know you said you didn't want to go through every line of code, but since connections can be passed around (or not) you may not have much of a choice. Something that might help is using this regex (works only in the VS find window):

//matches any constructor call to SqlConnection    
new:Zs*(System.)*(Data.)*(SqlClient.)*SqlConnection(\([0-9a-zA-Z]*\))

This will help you find your connection initializations, which would probably help speed things up. I think TomTom is right in that you are probably dealing with crappy code that has high cyclomatic complexity, so without a visual inspection, you probably won't find every case. Be careful to check whether the connections are being passed around. You probably have a painful refactor coming, but I promise it will pay off in spades.

Good luck, hope this helps!