What changed in ADO connection disposal between .net 3.5 and 4.5?


  • kills Dumbledore

    I'm googling around for this but I thought it would be worth asking you bunch of reprobates too.

    The software I'm working on has a lot of places it opens SQL connections and doesn't explicitly close them. We didn't notice before (the code was bought from the previous developers so nobody here has more than about a year's experience with it), because the database connections seemed to close themselves eventually so the number of open connections was never high enough to cause a significant problem.

    In the latest release, we've updated it to target .net 4.5 instead of 3.5, and suddenly we're seeing users who keep the app open steadily increase to a few thousand connections before they close it at the end of the day. There's also a service that never normally exits, which gets to ridiculously high levels and is now requiring a restart regularly, otherwise performance absolutely tanks.

    We have a guy going through and trying to find every instance of a connection that's left open and making sure they get closed, but I've been asked to do some research as to what might have changed. I would guess that either the garbage collector has changed and as a side effect the connections aren't cleaned up any more, or maybe that the connection pool is now more biased towards handing out a new connection rather than somehow recycling old ones? I'm not really sure, any insight would be welcome.

    Some information, if it's important: The app is written in VB.Net, it isn't installed on users' PCs, but run as a remote app so everyone gets a separate instance but it's all running off one server. There are a couple of hundred users logged in at a time.


  • FoxDev


  • kills Dumbledore

    I found this

    In the .NET Framework 4.5, non-pooled SQL connections which are not explicitly exposed (via Dispose, Close, or using) will leak memory

    But this says it was fixed in 4.5.1, and we're on 4.5.2. Also, the project is some portability analyser library and it's not clear if the documentation is referring to actual .Net connections or something specific to the project



  • @Jaloopa Did your database server change, to stop disconnecting idle users? Generally speaking, a DB connection lasts exactly as long as the DB server says it does. It doesn't seem likely to me that a change in ADO.net would cause this.

    Alternative suggestion: if the code wasn't using using, but was handling exceptions, any missing finally to close out the connection would leak one. Are users of the app creating more exceptions than in the past?


  • kills Dumbledore

    @blakeyrat said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    if the code wasn't using using, but was handling exceptions, any missing finally to close out the connection would leak one

    It seems a lot of the open calls just omitted the matching close/dispose calls entirely. It's possible that these connections were always there but people are now hitting exceptions in the places where there's a close outside a finally, but it seems unlikely. There are definitely a lot of places where connections have been being leaked for a long time (one screen opens 12 connections and doesn't close any of them), hence the suspicion that the connection objects either aren't being GCd or that the GC used to close before discarding and doesn't any more.

    My connection pool guess is all wrong, since the connections aren't pooled.

    @blakeyrat said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    Did your database server change, to stop disconnecting idle users?

    Not that I know of. It's on SQL Server 2008, so not like it's being actively updated. Might be worth double checking though.



  • @Jaloopa said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    It seems a lot of the open calls just omitted the matching close/dispose calls entirely.

    I hope your guy who's fixing it is using using.

    @Jaloopa said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    hence the suspicion that the connection objects either aren't being GCd or that the GC used to close before discarding and doesn't any more.

    @Jaloopa said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    My connection pool guess is all wrong, since the connections aren't pooled.

    ADO.Net does connection pooling by default-- are you 100% sure they aren't pooled? It would be extremely weird to turn that feature off.


  • kills Dumbledore

    @blakeyrat said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    I hope your guy who's fixing it is using using.

    It's not that easy. Some connection objects are passed between methods and other weirdness. There's a custom object that wraps the standard sqlconnection, and takes a sqlconnection as a constructor parameter. This object is used in about 5% of the places that create a connection, the rest are standard SqlConnections. He's using using where he can and being more creative where necessary. The whole thing is a mess.

    are you 100% sure they aren't pooled? It would be extremely weird to turn that feature off

    By default, all connection strings are created with pooling=false. There's an app.config setting that allows usernames to be specified for turning pooling back on. This setting contains no usernames in the source control version of the file. The live deployment might have some usernames overriding it, but the standard appears to be non pooling.



  • @Jaloopa

    It gets worse than that.

    If you open and close them, the ADO connections slowly leak memory.... for no apparent reason. You can even dispose them yourself.

    I don't know if they fixed that yet.



  • @xaade said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    If you open and close them, the ADO connections slowly leak memory.... for no apparent reason. You can even dispose them yourself.

    Maybe leak memory, but they won't leak DB connections, which is the actual problem here. Imagine the poor server, having to juggle 1000+ connections, 99.5% of which are idle and will always be idle.


  • kills Dumbledore

    @xaade said in What changed in ADO connection disposal between .net 3.5 and 4.5?:

    the ADO connections slowly leak memory

    Memory on the database server is an issue, but not as much as cpu. That increases in line with the number of open connections.