none
Timeout expired. RRS feed

  • Question

  • "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."

    I'm getting this error, and the server was barely under any load at all, despite having disabled all timeouts that I'm aware of. For instance, my connection string contains "Connection Timeout=0;" and the SqlCommand object (I'm working from within C#) has CommandTimeout set to 0. I'm using the SqlDataAdapter to open the connection, and close the connection immediately after completion (so connections are NOT being left open). Connection pooling is using the default settings (I believe default installation of SQL Server 2012 has it enabled).

    Why would this exception be thrown? I don't care how long it has to wait, it's better it waits 48 hours to finish than to throw an exception. Anyone encountered this before?


    • Edited by Fleurin Sunday, September 23, 2012 10:45 AM
    • Moved by Tom Phillips Tuesday, September 25, 2012 1:58 PM .Net question (From:SQL Server Database Engine)
    • Moved by Allen_MSDNModerator Wednesday, September 26, 2012 2:07 AM related topic (From:ADO.NET Entity Framework and LINQ to Entities)
    Sunday, September 23, 2012 10:44 AM

Answers

  • If you are getting this error with a timeout of 0, then you are likely hitting this issue: http://support.microsoft.com/kb/948868

    Basically this is a timing issue between opening connections and calling SqlConneciton.ClearPool or ClearAllPools. The hotfix above helps with the issue, but does not entirely fix it (note that .Net 4.0 also includes some additional fixes, but does not completely resolve the issue).

    Additionally, the issue can be made worse if you have many connections that are having errors. If so, you should consider upgrading to .Net 4.5 or installing the Reliability Update 1 for .Net 4.0: http://support.microsoft.com/kb/2533523


    Daniel Paoliello [MSFT]
    ADO.Net Managed Providers and DataSet

    Thursday, October 18, 2012 12:39 AM
    Moderator

All replies

  • How do you handle connections? I mean open/close? Looks like  you do not properly close the connection object.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Sunday, September 23, 2012 10:52 AM
  • Again, I'm using SqlDataAdapter, and I'm letting the SqlDataAdapter open the connection (so it closes it also). This completely automates the handling of the connection. I don't have to use a 'using' block, I don't have to call .close().

            private DataSet SomeDBOperation()
            {
                SqlConnection TheConnection = new SqlConnection(
                    "Data Source=localhost;Initial Catalog=XXXXXXX;User Id=XXXXXXX;Password=XXXXXXXX; Connection Timeout=0;");
                SqlCommand TheCommand = new SqlCommand();
                TheCommand.CommandText = "web_proc_getXXXXXXXX";
                TheCommand.CommandTimeout = 0;
                TheCommand.CommandType = System.Data.CommandType.StoredProcedure;
                TheCommand.Connection = TheConnection;
                SqlDataAdapter TheAdapter = new SqlDataAdapter(TheCommand);
                DataSet TheDataSet = new DataSet();
                TheAdapter.Fill(TheDataSet);
                return TheDataSet;
            }

    What are the "uncommon" causes for a timeout to be thrown?




    • Edited by Fleurin Sunday, September 23, 2012 11:11 AM
    Sunday, September 23, 2012 11:03 AM
  • Hi Fleurin,

    I changed the following connection string with my local SQL Server connection string, and tested the codes on my computer, found they worked. This issue can occur if the connection string is incorrect.

    Data Source=localhost;Initial Catalog=XXXXXXX;User Id=XXXXXXX;Password=XXXXXXXX; Connection Timeout=0;

    I suggest modifying “localhost” to your SQL Server instance name first, and then use the following codes to get the detail error message:

    try
                {
                    SqlConnection TheConnection = new SqlConnection(
                       "Data Source=localhost;Initial Catalog=XXXXXXX;User Id=XXXXXXX;Password=XXXXXXXX; Connection Timeout=0;");
                    TheConnection.Open();
                }
                catch (Exception ex)
                {
    
                    Response.Write(ex.ToString());
                }
    


    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 24, 2012 3:27 AM
  • Allen, the connection string I'm using is both valid and works; it works virtually every time. The connection just doesn't seem to work 100% of the time, and when it fails it throws the exception I've described above. I already have the full exception details (my application emails me the contents of the exception whenever something goes wrong, which is also how I discovered this rare crash), there's no point to trying the code that you've pasted.

    Your recommendation to use the server name rather than localhost might be a good idea, and I'll probably make that change, but I'm certain it's not the cause of the problem.

    Monday, September 24, 2012 4:40 AM
  • Hi, I understand that we can connect to the SQL Server instance most of time, and received the timeout error message sometimes. This issue can occur if a connection is opened and something is done with it, but then the connection is not closed. At this time, I want to confirm whether you open SQL Connection in other places, if so, please make sure you close it after using. For example:

    SqlConnection cnn = new SqlConnection(myConnectionString); 
    try
    {
        cnn.Open();
    // ...
    // ...do something with the connection
    // ... 
    }
    finally
    {
        cnn.Close();
    }
    

    For more detail information, please refer to the following document:

    http://msdn.microsoft.com/en-us/library/aa175863(SQL.80).aspx


    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 24, 2012 9:11 AM
  • Your question is a .Net programming question, not a SQL Server question.  You will get better answers from the .Net forums.

    However, once the .Net connection pool is exceeded, you can no longer connect.  This happens when you don't explictly close() the connection and the connections exceed the default .Net connection pool of 50.

    Monday, September 24, 2012 2:42 PM
  • The default maximum pool size not 50, it's 100, and I really doubt that that had been exceeded. Also, and I've said this a few times, you don't close a SQLConnection that had been passed to a SQLDataAdapter if that SQLConnection was not opened prior to passing it.

    I'd appreciate it if you made yourself familiar with the following MSDN articles before pompously deciding what my question is or isn't:

    • http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
    • http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx
    Tuesday, September 25, 2012 4:44 AM
  • Tuesday, September 25, 2012 7:39 AM
  • Moving to a more approriate .Net forum.

    Tuesday, September 25, 2012 1:56 PM
  • If you are getting this error with a timeout of 0, then you are likely hitting this issue: http://support.microsoft.com/kb/948868

    Basically this is a timing issue between opening connections and calling SqlConneciton.ClearPool or ClearAllPools. The hotfix above helps with the issue, but does not entirely fix it (note that .Net 4.0 also includes some additional fixes, but does not completely resolve the issue).

    Additionally, the issue can be made worse if you have many connections that are having errors. If so, you should consider upgrading to .Net 4.5 or installing the Reliability Update 1 for .Net 4.0: http://support.microsoft.com/kb/2533523


    Daniel Paoliello [MSFT]
    ADO.Net Managed Providers and DataSet

    Thursday, October 18, 2012 12:39 AM
    Moderator