none
How to solve connection leak issue in .NET Framework 2.0 RRS feed

  • Question

  • Hi,

    Issue - I have Connection Leak problem. There are too many connection remains opens in database. I am using Enterprise Library 3.1's Data Access Block.

    Could you suggest ways and means to solve the problem?

     

    Thank for your help!

    Thanks,

    Kalai

    • Moved by Bob Beauchemin Monday, July 12, 2010 7:17 PM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Monday, July 12, 2010 4:04 PM

Answers

  • Hi,

    If you are using DAAB with DataReader, you could do this :

     

    using (IDataReader dataReader = db.ExecuteReader(cmd))
    {
      while (dataReader.Read())
      {
         //your code here
      }
    }
    
    

    Using "using" will cause both the DataReader and connection to be closed. ExecuteReader will close the connection when the DataReader is closed.

    Thanks.

     

    • Proposed as answer by Dev-FX Tuesday, July 13, 2010 1:45 PM
    • Marked as answer by Alex LiangModerator Monday, July 19, 2010 8:26 AM
    Tuesday, July 13, 2010 1:25 PM
  • Hi kalai,

    Please check your application to make sure all database connections are closed when they are not needed. You can use the following two methods to avoid connection leak.

          SqlConnection conn = new SqlConnection(myConnectionString);

          try

          {

                conn.Open();

                doSomething(conn);

          }

          finally

         {

                conn.Close();                

         }

    Or

          using (SqlConnection conn = new SqlConnection(myConnectionString))
         {
               conn.Open();
               doSomething(conn);
          }

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 15, 2010 7:07 AM
    Moderator
  • Hi Kalai,

    Connection Lifetime only specifies the minimum number of seconds the connection should remain in the connection pool before it is removed. It does not automatically close an existing connection. When a connection is returned to the pool (e.g. by calling conn.Close()), it is immediately destroyed only if current_time - creation_time > [Connection Lifetime]. Otherwise it remains in the pool, and can be reused.

    SqlConnection.ConnectionString Property:
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

    ADO.NET Connection Pooling at a Glance:
    http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx?msg=3322599#xx3322599xx

    Marcel

    Thursday, July 15, 2010 9:57 AM
  • Kalai,

    This is really weird. When you write something like:

    using (SqlConnection conn = new SqlConnection(myConnectionString)) 
    {
     // Implementation skipped 
    }
    

    the connection will be closed and disposed off when code execution exits the using block. There is no way conn could be in an open state or even referenced outside of this block.

    The only way I can imagine this happening, is when you use DAAB but pass a connection object to one of the DAAB methods and then forget to close the connection. But in this case DAAB is not responsible for closing the connection (as it does when you pass a connection string or use a database factory), it is the programmer's responsibility to close the connection as soon as the database job is done.

    As for the connection pool - I'm afraid that you misunderstood its use. The limits are ment to govern over the connection pool used by the current client instance, not over the database's connections. Since opening and setting up a connection is resource intensive, the connection pool aleviates this issue by reusing active database connections. When the limit is reached, connection requests queue up. 

    The high number of connections you are seeing in your database is probably a result of multiple clients calling into the database (Max Pool Size x number of clients).

    Marcel

    • Marked as answer by Kalai.Mamani Saturday, July 24, 2010 11:51 AM
    Friday, July 23, 2010 11:50 AM

All replies

  • Hi,

    If you are using DAAB with DataReader, you could do this :

     

    using (IDataReader dataReader = db.ExecuteReader(cmd))
    {
      while (dataReader.Read())
      {
         //your code here
      }
    }
    
    

    Using "using" will cause both the DataReader and connection to be closed. ExecuteReader will close the connection when the DataReader is closed.

    Thanks.

     

    • Proposed as answer by Dev-FX Tuesday, July 13, 2010 1:45 PM
    • Marked as answer by Alex LiangModerator Monday, July 19, 2010 8:26 AM
    Tuesday, July 13, 2010 1:25 PM
  • Hi,

    I got your Answer and thank you so much.

    I am looking at the options like specifying Connection LifeTime. Thereby I need not change code.

    If i specify Connection LifeTime in connection string. Will the connection close automatically? Does this has any implications?

    Thanks,

    Kalai

     

     

     

     

     

    Tuesday, July 13, 2010 2:49 PM
  • Hi kalai,

    Please check your application to make sure all database connections are closed when they are not needed. You can use the following two methods to avoid connection leak.

          SqlConnection conn = new SqlConnection(myConnectionString);

          try

          {

                conn.Open();

                doSomething(conn);

          }

          finally

         {

                conn.Close();                

         }

    Or

          using (SqlConnection conn = new SqlConnection(myConnectionString))
         {
               conn.Open();
               doSomething(conn);
          }

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 15, 2010 7:07 AM
    Moderator
  • Hi Kalai,

    Connection Lifetime only specifies the minimum number of seconds the connection should remain in the connection pool before it is removed. It does not automatically close an existing connection. When a connection is returned to the pool (e.g. by calling conn.Close()), it is immediately destroyed only if current_time - creation_time > [Connection Lifetime]. Otherwise it remains in the pool, and can be reused.

    SqlConnection.ConnectionString Property:
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

    ADO.NET Connection Pooling at a Glance:
    http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx?msg=3322599#xx3322599xx

    Marcel

    Thursday, July 15, 2010 9:57 AM
  • Hi,

    I am using "Using" statement as you suggested. Still connections are getting closed.

    I am using Enterprise Library 3.1 Data Access Block.

     

    Any recommandations please?

    Thanks,

    Kalai

     

     

     

    Wednesday, July 21, 2010 3:40 PM
  • Hi Kalai,

    If you are using "Using" statement, the connection will be closed at the end of the "Using" statement. So what you have said, "I am using "Using" statement as you suggested. Still connections are getting closed." is right. Could you please clarify what recommandations do you need ? Thanks.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 22, 2010 2:33 AM
    Moderator
  • Hi Alex Liang,

    I do use "Using" statement. The connections are closed in every 2 to 3 hours (i.e. whenever GC runs). There are hundreds of opened connections in database. Used sp_who2 stored procedure to confirm this.

    Pool size is also specified in connection string. Min Pool size = 5 and Max Pool size = 10.

    My issue is too many connection opened in database. Want to see only 10 connections (max pool size configured) in database.

    Thanks,

    Kalai

     

    Friday, July 23, 2010 10:57 AM
  • Kalai,

    This is really weird. When you write something like:

    using (SqlConnection conn = new SqlConnection(myConnectionString)) 
    {
     // Implementation skipped 
    }
    

    the connection will be closed and disposed off when code execution exits the using block. There is no way conn could be in an open state or even referenced outside of this block.

    The only way I can imagine this happening, is when you use DAAB but pass a connection object to one of the DAAB methods and then forget to close the connection. But in this case DAAB is not responsible for closing the connection (as it does when you pass a connection string or use a database factory), it is the programmer's responsibility to close the connection as soon as the database job is done.

    As for the connection pool - I'm afraid that you misunderstood its use. The limits are ment to govern over the connection pool used by the current client instance, not over the database's connections. Since opening and setting up a connection is resource intensive, the connection pool aleviates this issue by reusing active database connections. When the limit is reached, connection requests queue up. 

    The high number of connections you are seeing in your database is probably a result of multiple clients calling into the database (Max Pool Size x number of clients).

    Marcel

    • Marked as answer by Kalai.Mamani Saturday, July 24, 2010 11:51 AM
    Friday, July 23, 2010 11:50 AM