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 to 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.
+3
A:
- 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.
TomTom
2010-04-12 17:13:23
+1
A:
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!
Audie
2010-04-12 17:57:58