Max pool size was reached


Have you ever encountered this error message on your application:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached.”

This problem occurred most probably because of connection leak. Either the connection string do not close properly or consistently.


When you intend to close your database connection, you want to make sure that you are really closing it. The following code looks fine yet causes a connection leak:



     SqlConnection conn = new SqlConnection(myConnectionString);


      conn.Open();


      doSomething();


      conn.Close();     
           


If doSomething() throws an exception – conn will never get explicitly closed. Here is how this can be corrected:



     SqlConnection conn = new SqlConnection(myConnectionString);


      try


      {


            conn.Open();


            doSomething(conn);


      }


      finally


     {


            conn.Close();                


      }


When returning a connection from a class method – make sure you cache it locally and call its Close method. The following code will leak a connection:



     OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());


      intres = cmd.ExecuteNonQuery();


     getConnection().Close(); // The connection returned from the first call to getConnection() is not being closed.

Instead of closing your connection, this line creates a new one and tries to close it.


 


Here are some solutions that you can try to solve the problem:


1) Check your application to make sure all database connections are closed when it is not needed.  ASP.NET is supposed to have garbage collector to reclaim unused resource.  However, on a busy site, it is likely that the connection pool will run out of connections before garbage collection kicks in.


2) You can raise the connection pool size in the connection string.  For example, you can add “Max Pool Size=100” to your connection string to increase the pool size to 100.


Comments (11)

  1. This can also be acomplished with using keyword

       using (SqlConnection conn = new SqlConnection(myConnectionString))

       {

               conn.Open();

               doSomething(conn);

       }

  2. Paresh says:

    What will happen in using if doSomething throwing any exception. Is connection get closed?

  3. Rob says:

    Yes Paresh that using will close a connection even if an exception occurrs.

  4. Kek says:

    Hi

    I know this post has been here for a long time but I've been experiencing this max pool issue in my app.

    Please how can I cache locally the connection returned from the class method?

    Thanks for your help

  5. Vladek says:

    Kek, he just ment to use something like that:

    var cachedConnection = getConnection();

    cachedConnection.Close();

  6. guddu says:

    it clear my issue abou max pool size

  7. Sulabh says:

    If there is a large project  , then there is any solution to find where the connection couldn't closed properly or check each page manually

  8. Sulabh Singla says:

    If there is a large project  , then what's the solution to find where the connection didn't closed properly or we have to check each page manually

  9. Olimat says:

    Nice , but What is the effect of increasing the pool size … does it effect on the Server RAM , CPU ….

    since the default is 100 , then i guess  increasing it will effect on some where else.  

    thnx in Advance ^_^

  10. rahul says:

    please check your network connection.

  11. Bubble says:

    How can I cache locally the connection returned from the class method?

    my connection is in another class

    eg:

    public SqlConnection GetConnection

           {

               get

               {

                   if (_oConn == null)

                   {

                   string sConnString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;

                       _oConn = new SqlConnection(sConnString);

                   }

                   return _oConn;

               }

           }      

    in my other webform, i call the connection and open it like below

    oConn.GetConnection.Open();

    do something

    finally

    {

    oConn.GetConnection.Close();

    }

    It is my connection is not get close and this has connection leak.  How I can fix it?  Please help.