none
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

    Question

  • All,

    In some reason, my application got timeout expired exception. In my code, I close Sql connection in finally block. So that should not have connection leak. Also, I monitor my application from SQL management studio and the max concurrent connections is 5 and should be less than default Max Pool Size (100).

    Since I don't specify value of Max Pool Size and Min Pool Size in connection string, I think they should be 100/0.

    I list sample code as following and thanks for any help.

    Connection string.
    Application Name=AppName;Data Source=ServerName;Initial Catalog=DBName;User ID=xxxx;Password=yyyy;Connection Timeout = 0;

    public static void GetData(SqlCommand cmd, String connString, ref DataTable dt)
    {
        SqlConnection Conn = null;
         Conn = new SqlConnection(connString);
          if( null != Conn)
          {
                try
                {
                     cmd.Connection = Conn;
                      cmd.CommandTimeout = 0;
                      SqlDataAdapter sad = new  SqlDataAdapter(cmd);
                     Conn.Open();
                     if( Conn.State == ConnectionState.Open)
                     {
                            sda.Fill(dt)
                     }
                     sda.Dispose();
                  }
                catch(Exception ex)
                {
                        // log exception
                }
                finally
                {
                    if(null != Conn)
                    {
                            if(Conn.State == ConnectionState.Open)
                            {
                                    Conn.Close();
                            }
                    }
                }
            }
    }

    Exceptions:
    [ExceptionName: InvalidOperationException]
    [Message: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.]
    Monday, April 09, 2007 10:19 PM

All replies

  • This is a very common issue. It can be caused by a number of factors that include (but are not limited to):

    1) You have failed to close connections. In your case I would suspect that this is not the issue as you have forced the connection to close in a number of ways. Incientally, you don't have to open or close a connection when you use the Fill method. ADO.NET handles this for you.

    2) If you press the server too hard (as with inefficient queries or an under-configured server) or other factors prevent the server from getting the query complete before another is started, an additional connection must be added to take up the load. The server can handle about 20 of these (typically) in a well-tuned system. I expect this is the issue in your case.

     

    See Chapter 9 of my book for more details and diagnostic tools.

     

    hth

    Tuesday, April 10, 2007 1:00 AM
    Moderator
  • That is a good point in #2. However, I am not quite understanding your explanation on that. I don't know why this caused timeout expired exception which shows max pool size reached. If that is case, it should show a lot of connection created from SQL management studio. I will try and can't think this can 100 percent fix this problem. Or .NET framework returns wrong or mislead exception to me in this case.

    Could you please give me more detail about #2? and the name of the book you mentioned.

    Thanks for your time.
    Tuesday, April 10, 2007 3:58 AM
  • There have a very interesting thing of Connection Timeout tag in connection string. If I change value of this tag to anything great than 0, the problem is gone. That looks like ADO .NET doesn't like zero value in this tag. I can't explain why zero cause this problem but looks like problem solved. If you have any idea about this tag, please add your comments.

    Thanks,
    Tuesday, April 10, 2007 6:53 PM
  • Setting the Connection Timeout to 0 should mean an infinite timeout. I can't imagine why that is solving your problem, unless somewhere you have connections that are running forever and maxing out the thread pool, and setting a timeout causes them to be terminated, freeing up available connections. But I would think that you would see evidence of that.
    Wednesday, April 11, 2007 4:21 PM
  • By setting the Timeout to 0 (infinite) your application blocks until a connection is available. This means the user waits for N seconds and then times out--closing the page and giving up on your site and releasing the connection for others to use. Is this what you had in mind?

    I think you probably want to to address the underlying cause. Consider that of the of sites we've studied or heard about over the years, even the most serious, heavily used sites did not use more than about 25 connections at once. Beyond that, the server was flooded.

     

    Wednesday, April 11, 2007 7:58 PM
    Moderator
  • I used performance monitor to find out how many connection there had in connection pool when connection timeout set to 0. The total connections were 5. Since I haven't set up Max Pool Size and Min Pool Size, ADO .NET should use default value 100/0. I don't think the total connections reach the maximum size. As my understanding, ADO .NET open a new connection if all available connections are used based on connection string. I know the zero value of timeout is not a good idea in real world and don't understand why this will cause Sqlconnection.Open method failed.
    Maybe there have reason to limit this kind of scenario and I can't find out from MSDN document.


    Thursday, April 12, 2007 4:29 AM
  • Okay, the default connection timeout is 60 seconds. If the server is so busy that it can't service the open request, I can see how it would timeout. The generic exception message takes a shotgun approach--including text that many developers find as the cause. If you pool is not full, it definitely leads me to think that the server is pegged and can't service the request. The only other issue might be that the admin has set up a configuration that only accepts 5 cals (connections)...
    Friday, April 13, 2007 12:29 AM
    Moderator