none
Why connections are not disposed()? RRS feed

  • Question

  • I have very simple reproduction scenario. Code is below. I basically open 10 connections to database and does not explicetely close them each time Ok button is clicked. I monitor .NET Provider For SQLServer\NumberOfPooledConnections. After 10-15 times of hitting Ok button, this number eventually settled at 37 on a form. One for Clearing All Pools and another one explicetly calls GC.Collect(). Hitting any of those buttons does not clear those connections either.>Why would connection linger in memory if ASP.NET page finished executing and GC already run?

     

    
    
    
    
    	 protected void ButtonInitiateConnection_Click(object sender, EventArgs e)
      {
       for (int i = 0; i < 10; i++)
       {
        SqlConnection connection = new SqlConnection("server=localhost;Integrated Security=SSPI;Initial Catalog=test");
        connection.Open ();
        
       }
    
      }
    
      protected void ButtonClearPools_Click(object sender, EventArgs e)
      {
       SqlConnection.ClearAllPools();
       
      }
    
      protected void ButtonCallGC_Click(object sender, EventArgs e)
      {
       System.GC.Collect();
      }
    

     

     

     

    Friday, September 24, 2010 2:58 PM

Answers

  • Since you have not called Close() on the SqlConnections that you have made, the thread pool believes that they are still in use, hence why calling ClearAllPools() does not cause them to be removed from the pool. Additionally, the GC will not collect the SqlConnection objects since the Connection Pool still holds a reference to them.

    The best way to ensure that connections are released properly is a 'using' statement as shown in the SqlConnection example on the MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

    Friday, September 24, 2010 8:49 PM
    Moderator
  • Some of those may be considered idle and after a certain period of time will be removed from the connection pool. This is especially true of connections that have been explicitly released. There could be other factors involved as well such as your system configuration with respect to the ASP.NET app and the authentication method you are using for SQL Server.

    Based upon your experiences, do you really want to rely on the system to clean up after you? ;-)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 9:13 PM
  • Well according to Daniel's post, a SQLConnection is not GC'd if it is still referenced by a connection in the connection pool. Since the Close or Dispose was never called then that reference will remain.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, September 27, 2010 4:08 PM
  • Hi Lorry,

    Here is a great source of information about .Net garbage collection:

    http://blogs.msdn.com/b/tess/archive/2007/04/10/net-garbage-collector-popquiz-followup.aspx

    I hope it helps!

    Thanks,

    Cathy Miller

    Microsoft Online Community Support

    Thursday, October 7, 2010 7:53 PM
    Moderator

All replies

  • Are the connections in use when you invoke ClearAllPools? If so, they will remain until Close is called on each.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 4:59 PM
  • All the code is in original post. None of the connections are used since they are just open and that's it. My understanding that after ASP.NET page finished executing all those objects will fall out of scope and hence shall be destroyed by GC collector. ClearingAllPools() or Collect() still leaves them in memory. Try this code for yourself and monitor those numbers.
    Friday, September 24, 2010 5:01 PM
  • With respect to the SQLConnection Class, unless I understand incorrectly (in which case someone will correct me) I don't believe that Close will be called by the Dispose finalizer under this scenario. So essentially the connection resource is never released back to the connection pool and would be considered "in use".

    You should always call either the Close or Dispose method explicitly (which will release the connection resource) when you are finished with a connection.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 5:42 PM
  • So there are some objects there which will live in memory forever even after they fall out of scope? Is not it recipe for managed memory leak?
    Friday, September 24, 2010 5:45 PM
  • In the case of a SQLConnection, which operates with resources outside of .NET, I would say that is true. It's true in other instances as well, such as COM Interop.

    If you are a lazy coder and don't clean up after yourself, which I'm sure we have all done from time to time, then you're probably asking for trouble.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 6:13 PM
  • Since you have not called Close() on the SqlConnections that you have made, the thread pool believes that they are still in use, hence why calling ClearAllPools() does not cause them to be removed from the pool. Additionally, the GC will not collect the SqlConnection objects since the Connection Pool still holds a reference to them.

    The best way to ensure that connections are released properly is a 'using' statement as shown in the SqlConnection example on the MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

    Friday, September 24, 2010 8:49 PM
    Moderator
  • Since you have not called Close() on the SqlConnections that you have made, the thread pool believes that they are still in use, hence why calling ClearAllPools() does not cause them to be removed from the pool. Additionally, the GC will not collect the SqlConnection objects since the Connection Pool still holds a reference to them.

    The best way to ensure that connections are released properly is a 'using' statement as shown in the SqlConnection example on the MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

    Data is pretty incosistent with actual behavior. Some of connections are being release and some are not. After clicking Button for 10 times it as expected creates 100 connections (you can see those in Perfmon) but after a while that number will go down to around 30 or so. So they are released at some point without calling any additional methods. So why some connections will stay alive and some will vanish?
    Friday, September 24, 2010 8:54 PM
  • Some of those may be considered idle and after a certain period of time will be removed from the connection pool. This is especially true of connections that have been explicitly released. There could be other factors involved as well such as your system configuration with respect to the ASP.NET app and the authentication method you are using for SQL Server.

    Based upon your experiences, do you really want to rely on the system to clean up after you? ;-)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 9:13 PM
  • Some of those may be considered idle and after a certain period of time will be removed from the connection pool. This is especially true of connections that have been explicitly released. There could be other factors involved as well such as your system configuration with respect to the ASP.NET app and the authentication method you are using for SQL Server.

    Based upon your experiences, do you really want to rely on the system to clean up after you? ;-)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    I'm not trying to way not to Close() or Using(){}, I'm trying to understand how SQLClient works inside .NET domain and so far I can not figure out exact mechanics of what is happening in the background. My understanding that even if I don't explicetly destroy something when ASP.NET finishes execution then all those objects will fall out of scope and shall be reclaimed by GC but it's not happening. This fundamental question which I'm trying to understand. This is probably applicable to other scenarios as well it just happens that it's easy to see if object is still in memory based on Perfmon for SQLConnection but not easy for other objects.
    Friday, September 24, 2010 9:17 PM
  • Well according to Daniel's post, a SQLConnection is not GC'd if it is still referenced by a connection in the connection pool. Since the Close or Dispose was never called then that reference will remain.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, September 27, 2010 4:08 PM
  • Hi Lorry,

    Here is a great source of information about .Net garbage collection:

    http://blogs.msdn.com/b/tess/archive/2007/04/10/net-garbage-collector-popquiz-followup.aspx

    I hope it helps!

    Thanks,

    Cathy Miller

    Microsoft Online Community Support

    Thursday, October 7, 2010 7:53 PM
    Moderator