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

    Question


  • Hi

    My application is working with a DataBase server which is connected through VPN.

    Some time i get an exception like...

    "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. Inner Exception:>>"

    Can you please let me know if you have come across any such scenario in your application & any solution for it? 

    Wednesday, December 20, 2006 10:56 AM

Answers

  • This is an all-too-common complaint. While increasing the size of the connection pool might help, it's like getting a bigger bucket to catch the rain coming in the roof. A full-blown commercial ASP.NET site might use a couple of dozen connections at once. If 50 is not enough there is a serious issue. However, in your case if this is a Windows Forms application connecting over a VPN (as I think you said it was), the Connection pool is irrelevant. What is relevant is the delays that are often caused by slow WAN traffic over the VPN. Yes, increase the Connection and Command Timeout values to compensate and add code to your exception handlers to deal with this contingency.

     

    That said, even a Windows Forms application can overfill the ConnectionPool (even though it's not necessary in many applications). Make SURE you call Connection.Close (not just Dispose or depend on Using) to clear the pool. You do NOT want to depend on the GC to close the connection when the system gets around to it. Note that you need a Connection.Close in the Finally block as well to close the Connection when there are exceptions.

     

    See Chapter 9 in my book for a detailed discussion of connection management.

     

    Saturday, May 24, 2008 7:36 PM

All replies

  • I believe you need to increase connection timeout in your application to be able to establish connection. When application tries to open connection, then it has default timeout around 30 seconds. If connection will not be established during this time, then provider generates an error. If you are using .NET, you could increase this time setting ConnectionTimeout property of the connection class.

    But if server is not visible at all, setting this property will not help and you would need to diagnose while server is not visible over VPN


    Val Mazur

    http://www.xporttools.net

    Wednesday, December 20, 2006 11:26 AM
  •  

    I recently started having this problem, and here is some of the informaiton that I discovered to help me resolve it.

     

    On your SQL connection string, there is an option (Max Pool Size) that you can specify to increase the size of your connection pool. The default is 100 if you don't specify any other value. The default value should be sufficient in most cases. If you determine that you need more, you can specify a higher value in your connection.

     

    Replys to other posts in this forum recommend turning off connection pooling to resolve the issue, but this will significantly increase your overheand and decrease your performance. I don't recommend this.

     

    Another reply recommends setting the connection timeout to 30 seconds. Again, I don't recommend this unless you are running on a cluster, because it will unnecessarily increase your application overhead and it won't really solve your problem. You are running out of unused connections in the pool, decreasing the timeout does not make used connections available sooner, so the only fix is to make more unused connections available.

     

    If you analyze your application and find out that you need to have more than 100 concurrent connections open to your database, then the only way to fix the problem is to increase the size of you connection pool by adding "Max Pool Size" to your connection string. Search for "Max Pool Size" in MSDN if you need more information on how to do this. Also, make sure that your DB server has enough memory to handle the extra connections.

     

    Most often the problem is because database connections are being opened but never closed. Eventually they will time out and the problem will go away for a while, but will soon return. If you are opening a connection inside a loop, try moving the open/close connections outside the loop or call .close() on the connection at the end of the loop. Examine your code and make sure that you close all of your database connections as soon as you are done with them, this will return your connection to the pool so it can be used by the next database operation. If you don't explicitly close the connection when you are done, it will stay open and out of the pool until it times out, which may be several minutes.

    • Proposed as answer by Mark Brislane Friday, April 15, 2011 8:26 AM
    Sunday, June 24, 2007 8:32 PM
  • im using 'Using' block whilw creating command n reader objects..

    it shud close the connection...but its not hapening..

    im using enterprise library.....

    here is my code....

     

    Dim GRIDDB As Database = ApplicationDatabase.CreateGRIDDatabase()

    Dim flag As Integer

    Try

    'Creating Transaction Options object

    Dim options As New TransactionOptions()

    'Setting isolation level to ReadCommitted

    options.IsolationLevel = IsolationLevel.ReadCommitted

    'Setting Transaction Timout as 2 mins.

    options.Timeout = New TimeSpan(0, 2, 0)

    Using thisTransactionScope As TransactionScope = New TransactionScope(TransactionScopeOption.Required, options)

     

    'Command for inserting lease information

    Using cmd As DbCommand = GRIDDB.GetStoredProcCommand("LeaseInformation_Insert")

    GRIDDB.AddInParameter(cmd, "PropertyID", DbType.String, leaseInfo.PropertyID)

    GRIDDB.AddInParameter(cmd, "LeaseStart", DbType.DateTime, leaseInfo.OriginalLeaseStart)

    GRIDDB.AddInParameter(cmd, "TermStart", DbType.DateTime, leaseInfo.CurrentTermStart)

    GRIDDB.AddInParameter(cmd, "LeaseExp", DbType.DateTime, leaseInfo.LeaseExpiration)

    GRIDDB.AddInParameter(cmd, "LeaseType", DbType.Int64, leaseInfo.TypeOfLease.ValueId)

    GRIDDB.AddInParameter(cmd, "RenewalOption", DbType.String, leaseInfo.RenewalOptions)

    GRIDDB.AddInParameter(cmd, "RenewalSummary", DbType.String, leaseInfo.RenewalSummary)

    GRIDDB.AddInParameter(cmd, "OptionToPurchase", DbType.String, leaseInfo.OptionToPurchase)

    GRIDDB.AddInParameter(cmd, "Tenant", DbType.Int64, leaseInfo.SingleMultipleTenant.ValueId)

    GRIDDB.AddInParameter(cmd, "CurrentMonthCost", DbType.Decimal, leaseInfo.CurrMonthlyCostRate)

    GRIDDB.AddInParameter(cmd, "LeaseRate", DbType.Decimal, leaseInfo.YearlyBaseLeaseRate)

    GRIDDB.AddInParameter(cmd, "OperatingRate", DbType.Decimal, leaseInfo.YearlyEstOperatingRate)

    GRIDDB.AddInParameter(cmd, "SecurityDeposit", DbType.String, leaseInfo.SecurityDeposit)

    GRIDDB.AddInParameter(cmd, "Renewal", DbType.String, leaseInfo.Renewal)

    GRIDDB.AddInParameter(cmd, "ExitInfo", DbType.String, leaseInfo.ExitDetail)

    GRIDDB.AddInParameter(cmd, "Refusal", DbType.String, leaseInfo.RightOfFirstRefusal)

    GRIDDB.AddInParameter(cmd, "IsComplete", DbType.String, leaseInfo.IsComplete)

    GRIDDB.AddOutParameter(cmd, "isError", DbType.Int32, 4)

    'executing the stored procedure

    GRIDDB.ExecuteNonQuery(cmd)

    flag = GRIDDB.GetParameterValue(cmd, "isError")

    cmd.Parameters.Clear()

    ' cmd.Connection.Close()

    End Using

    ' End If

     

    'Committing the transaction

    thisTransactionScope.Complete()

    End Using

    'If we reached this point then returning true

    If flag = 0 Then

    Return True

    End If

     

    Finally

    GRIDDB = Nothing

    End Try

     

    pls reply soon.its very urgent...

    Friday, January 11, 2008 3:41 AM
  • It is not clear what the actual issue is. Do you get opened connection? I also believe you do not need to use transaction if you execute only single INSERT SQL statement, since it is atomic anyway and adding transaction will only add overhead, but will not provide any value.

     

    Friday, January 11, 2008 12:07 PM
  • You need to do 2 things to fix the issue:

     

    1)

     

    Increase the Connection Timeout. By default it is 30 seconds. You can assign your own value to it.

     

    2)

     

    As other than timout you are also getting the error that

    "the 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. Inner Exception:>>"

     

    So you can also increase the Connection Pool Size. I am not sure but I think that the default size is 50. You can assign your value to it like 60 or 70.

     

     

    Friday, January 11, 2008 8:39 PM
  • Hi, I had the same problem. I cheked latter that my connecctions aren't closed or disposed, so, for each called to BD the connection stayed open, resulting in a series of conecction in queue. Then the error disappear..

    This article is old, however, this may be helpfull to everyone else.



    Friday, April 11, 2008 1:52 PM
  • I had always thought it was sufficient to set the connection to null to close it. However, that seems to have been wrong. By adding the statement below,

     

    if (cnnX.State == ConnectionState.Open) cnnX.Close();   

     

    my problem vanished and the code ran very quickly.

     

    try

    {

    if (cnnX.State == ConnectionState.Closed) cnnX.Open();

    cmdX.ExecuteNonQuery();

    }

    catch (Exception ex)

    {

    string strErrMsg = ex.Message;

    }

    finally

    {

    if (cmdX != null) cmdX = null;

    if (cnnX.State == ConnectionState.Open) cnnX.Close();    // <<<<--- New addition

    if (cnnX != null) cnnX = null;

    }

    Thursday, May 22, 2008 10:19 PM
  • This is an all-too-common complaint. While increasing the size of the connection pool might help, it's like getting a bigger bucket to catch the rain coming in the roof. A full-blown commercial ASP.NET site might use a couple of dozen connections at once. If 50 is not enough there is a serious issue. However, in your case if this is a Windows Forms application connecting over a VPN (as I think you said it was), the Connection pool is irrelevant. What is relevant is the delays that are often caused by slow WAN traffic over the VPN. Yes, increase the Connection and Command Timeout values to compensate and add code to your exception handlers to deal with this contingency.

     

    That said, even a Windows Forms application can overfill the ConnectionPool (even though it's not necessary in many applications). Make SURE you call Connection.Close (not just Dispose or depend on Using) to clear the pool. You do NOT want to depend on the GC to close the connection when the system gets around to it. Note that you need a Connection.Close in the Finally block as well to close the Connection when there are exceptions.

     

    See Chapter 9 in my book for a detailed discussion of connection management.

     

    Saturday, May 24, 2008 7:36 PM
  • Hi,

    I also came across same problem. I dont know my solution will solve your problem too as I was having problem in my own application (while you have third party VPN connection software).
    I had windows application and I was trying to open 4-5 connections one by one in seperate threads. So I was getting above error. The solution I got is - I increased some time between starting two threads (in that way, increased time between opening two connections). So it got solved.
    Thursday, June 11, 2009 12:49 PM
  • You said
    "Make SURE you call Connection.Close (not just Dispose or depend on Using) to clear the pool. You do NOT want to depend on the GC to close the connection when the system gets around to it. Note that you need a Connection.Close in the Finally block as well to close the Connection when there are exceptions."

    I totaly disagree with that assertion. I believe it is a good practice to use the "using" statement as it will call the dispose function when the program counter will reach the } that close the "using". The dispose fucntion of SqlConnection is going to call the Close function as you can see it with The Reflector :

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            this._userConnectionOptions = null;
            this._poolGroup = null;
            this.Close();
        }
        this.DisposeMe(disposing);
        base.Dispose(disposing);
    }
    
    the using keyword help for lisibility of the code. the using keyword has nothing to do with the GC (It is interpreted by the C# compiler) as the close method will be call when the program counter will reach the } of the using statement. The msdn explains that "The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler. " (http://msdn.microsoft.com/en-us/library/yh598w02.aspx).

    Could you, please, explain more deeply what is written in your book Chapter 9 ?
    Wednesday, November 25, 2009 12:34 AM
  • Sir where do i put this code in, which file i mean ?
    Friday, July 30, 2010 10:56 AM
  • My book and my comments are based on years of field experience. I have been able to solve leaking connection pools in a number of cases by REMOVING the Using statement and explicitly closing the Connection object. The questioner in this case was able to solve the problem in the same way.

    No, I agree, this should not make a difference. Yes, I too saw the generated code and thought that it should work. For some reason that I and those folks at Microsoft that reviewed the problem don't understand, it didn't. What did work was explicitly calling Close on the connection.


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Friday, July 30, 2010 10:22 PM
  • Hi William,

    A quick comment on your post.  Our site is used by upwards of 3000 people at any time.  Would you still only expect to see 50 connections?

    On some sales, we'd expect up to 50000 people to be looking at the site at any one time... again, would you still only expect 50 connections? 

    How can you see the number of connections in use at any time?  I currently look at SQL Server's "SP_WHO2" command to see connections to the database, however this is different to connections to the website itself.


    (Sorry for my naivity on this topic)

    Regards

    Andy

    Wednesday, September 15, 2010 10:08 AM
  • I just experienced the same problem.  I ended up using a pattern like this which seemed to fix the issue:

    using (SqlConnection con = new SqlConnection(strCon)) {
      using (SqlCommand cmd = new SqlCommand(strCmdText, con)) {
        con.Open();
        using (SqlDataReader dr = cmd.ExecuteReader()) {
        //do stuff;
          dr.Close();
        }
      }
      con.Close();
    }

    This seemed to fix my problem.  DataReader.Close() was the nail that did it.  It seems like MS should change their recommendation since I've found it all over their site suggesting not to use the try { } finally { con.Close(); } pattern.  I didn't try this explicitly, since the pattern is fairly pervasive throughout our entire db layer and wanted to find something closer.

    I hope this helps someone.

    Wes

    Tuesday, October 12, 2010 9:25 PM
  •  

    I got the below error randomly on my production site . The weird this is it's hold the  Guid (use as a primary key and base element that is used through out the application) .Hold means that each and every record related to that guid get corrupted .Interesting thing is I can insert a new record but it doesn't allow me to update any thing  related to that guid and produce the same error .I did try to restart my IIS my Database but the problem never go away .The only thing that resolved it to replace the existing corrupted guid with a newly generated guid  through out the the application database.

    The other thing that I did try is I replaced the connection string of my application on my local machine with the production connection string  in this case it worked perfectly .

    ErrorMessage:
    type=System.Data.SqlClient.SqlException
    message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
    stack= at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) at OpusFirst.SaveFieldController.SaveTextField(String loanGuid, String fieldName, String fieldValue, String additionalColumnName, String additionalColumnValue, String tableName, String databaseName, Boolean checkDiscrepancy, String UserID)

    Friday, February 04, 2011 8:51 PM
  • I just had a question about relying on "using" to close the connection. We have been seeing the timeout on the connection pool issue and have a huge amount of code that relies on "using" to close connections. Before we go ahead and change all of our code, I'd like to replicate the problem in a simple test harness to show our team that "using" does not indeed close the connection. Do you have a sample that would raise this error? We tried something like:

    SqlCommand cmd = new SqlCommand("select 1");
    for(int i = 0; i< 10000; i++)
    {
    SqlConnection conn = new SqlConnection("blah");
    cmd.Connection = conn;
    using(conn)
    {
    cmd.ExecuteQuery();
    }
    }

    And cannot replicate the issue. 

    Tuesday, March 15, 2011 9:43 PM
  • I'm a bit sceptical about calling Close() explicitly is different to the "using" statement.
    When using "using", it's not the Garbage Collector that closes the connection. The closing brace becomes a call to Dispose. This *explicitly* closes the connection.
    The GC would call Finalize, which itself would call Dispose. This *implicitly* closes it, at some unknown time.
    In the examples below, I've replaced the using by explicit calls to Close() in a try/finally block, and it made no difference whatsoever.

    Anyway, here's a bit of c#4 code that would run plenty of threads, each opening a DB connection and doing some work. I've artificially simulated long running statement (20 seconds) by using the WAIT FOR statement. This results in the same Exception as the initial poster System.Data.SqlClient.SqlException “Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.”.

    I believe this would be a genuine case of "you're doing a lot of hard work in your application, 100 parallel connections is not enough, increase the connection pool for this application." (or if you don't anything fancy or complicated, write better sql, add indexes, etc.. so that the queries return faster, which means the connections would be closed faster, therefore being available again in the pool). In Activity Monitor, there are 100 sessions shown, and none are blocked, they are all busy "doing things".

     

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Threading.Tasks;
     
    namespace ConsoleApplication3
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (IDbConnection conn = new SqlConnection(@"Data Source=localhost\SQLEXPRESS; Initial Catalog=MyDB; UID=myUser; Password=myPwd; Persist security info=false;"))
                {                 //breakpoint here to let you add a perfmon counter on ".Net Data Provider for Sql Server", "Number of Pooled Connections"                  //on this process instance which is only listed once the program has opened 1 connection.             }             Task[] tasks = new Task[500];             for (int i = 0; i < tasks.Length; i++)             {                 tasks[i] = Task.Factory.StartNew((Action)delegate { DoFullDBStatement(); });             }             Task.WaitAll(tasks);         }         private static void DoFullDBStatement()         {             using (IDbConnection conn = new SqlConnection(@"Data Source=localhost\SQLEXPRESS; Initial Catalog=MyDB; UID=myUser; Password=myPwd; Persist security info=false;"))             {                 using (IDbCommand command = conn.CreateCommand())                 {                     conn.Open();                     command.CommandText = "select * from MyTable WAITFOR DELAY '00:00:20'";                     command.CommandType = CommandType.Text;                     IDataReader reader = command.ExecuteReader();                 }             }         }     } }

     

    Now, another way to cause the same Exception, run this in Query Analyzer, and after a second, press the stop button. This results in a lock being held on this table, and it's not released because you pressed STOP.

     

    BEGIN TRANSACTION
    SELECT * FROM MyTable WITH (TABLOCKX, HOLDLOCK) WHERE 0 = 1
    WAITFOR DELAY '00:05:00'
    ROLLBACK TRANSACTION
    

     

    Then run the same Console App as above. It will crash with the same TimeOut exception, but this time, it's caused by the Command not returning in time, because there is a lock held on MyTable, each connection is waiting for that lock to be released and it doesn't get released, so it timesout. The callstack is different. In SQL Activity Monitor, you will see the column "Locked By" has an ID in it and the wait type is LCK_M_IS.

    To resolve this one, you have to find in your application who is keeping a lock and blocking everyone else.

    Note: you can make things happen quicker in testing by setting the pool size to a smaller number than the default of 100 (for example just add: Max Pool Size=10).

     

    Also check this recent post on msdn:
    http://blogs.msdn.com/b/sqlblog/archive/2011/05/25/ado-net-application-connecting-to-a-mirrored-sql-server-database-may-timeout-long-before-the-actual-connection-timeout-elapses-sometimes-within-milliseconds.aspx 
    • Edited by Thierry_S Friday, June 03, 2011 3:17 PM added msdn link
    Friday, June 03, 2011 3:14 PM
  • System.Data.SqlClient.

    SqlConnection.ClearAllPools();

    Kesin çözüm

    Thursday, June 09, 2011 7:45 AM
  • The number of connections that remain in the pool is a function of several factors. First, if the operation is efficient and fast, the number of users that the system can service/second goes up. Think of the connection pool like the line at Taco Time. If the clerks can't take the orders fast enough they open another register. The customers queue for the registers (just like SQL Server Connection Open operations queue for an available connection) and don't "time out" (go to another taco joint) unless the processing time overflows the capacity of the clerks at all available registers. So would adding cash registers help (adding to the Max Pool count)? Perhaps, but only if the taco stuffers in the back can keep up.

    So, without the analogy, the server can typically handle a full load with a relatively few number of connections. That's also because with web apps, the connections stay open for milliseconds while the query is being execute and the rowset is returned. If the app is written poorly or the query takes too long to execute the whole process breaks down.

    hth


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 07, 2011 4:43 PM
  • Could you give me your opinion on this situation

    I have an application in C# Framework 4.0. Like many app this one connects to a data base to get information. In my case this database is SqlServer 2008 Express.

    The database is in my machine

    In my data layer I’m using Enterprise Library 5.0

    When I publish my app in my local machine (App Pool Classic)

    ·         Windows Professional

    ·         IIS 7.5

    The application works fine. I’m using this query to check the number of connections my application is creating when I’m testing it.

    SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,loginame as LoginName

    FROM sys.sysprocesses

    WHERE dbid > 0 AND db_name(dbid) = 'MyDataBase'

    GROUP BY dbid, loginame

    When I start testing the number of connection start growing but at some point the max number of connection is 26. I think that’s ok because the app works

    When I publish the app to TestMachine1

    ·         XP Mode Virtual Machine (Windows XP Professional)

    ·         IIS 5.1

    I works fine, the behavior is the same the number of connections to the database increment to 24 or 26, after that they stay at that point no matter what I do in the application.

    The problem:

    When I publish to TestMachine2 (App Pool Classic)

    ·         Windows Server 2008 R2

    ·         IIS 7.5

    I start to test the application the number of connection to the database start to grow but this time they grow very rapidly and don’t stop growing at 24 or 26, the number of connections grow till the get to be 100 and the application stop working at that point.

    I have check for any difference on the publications, especially in Windows Professional and Windows Server and they seem with the same parameters and configurations.

    Any clues why this could be happening? , any suggestions?


    Luis Forero
    Tuesday, December 06, 2011 4:18 PM
  • Given that this thread has a number of very poor suggestions (increase the timeout, increase the pool size, flush the pool) that might address the symptoms, I can see how you're confused. These suggestions DO NOT solve the problem.

    You have a leaking connection pool. You MUST ensure that the connections are closed after use EACH time and EVERY time they are used. This means if something goes wrong, the connection must be closed--EXPLICITLY. You cannot depend on scope to close a connection (as you could with VB6), you can't expect the USING to work correctly (for reasons that I can't explain), you can't expect the garbage collector to close your connections. You MUST execute a Close method after the data is consumed in any application that does not have a persistent connection architecture. No ASP.NET (or similar) application can support that architecture. This means if you pass a DataReader to another layer in your application that layer must close the DataReader AND you must create the DataReader with the CloseConnection option.

    Folks, if you don't know how this mechanism works, don't bother answering the question.


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!


    Tuesday, December 06, 2011 6:08 PM
  • Thanks William for clearing this up. My question is, (and I know you said you can't explain it), why doesn't using work? It's just plain insane that "USING" does not guarantee the closing of a connection. This makes the whole system suspect. This means IDisposable is not reliable, it means that "USING" is not reliable. What else is not reliable?

    Someone from MS should really pipe in and explain why the USING statement does not close a connection.

    Tuesday, December 06, 2011 6:15 PM
  • I had a client that had an overflowing pool. We searched high and low for places in the code where the connections were getting opened but not closed. We found some but the problem did not go away. We finally isolated the problem to one routine that looked great, but it used the Using statement to close the connection. We looked at the IL and it looked correct. We removed the Using block and replaced it with an explicit Close and the problem went away. We asked Microsoft to explain (I taked to the ADO.NET engineers themselves) they could not. I just know that Using does not (always) work. It's one of those unsolved mysteries of life.

    "Using. Just say Close"

    hth


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, December 06, 2011 6:28 PM
  • I believe you, and it really does make me wary of the whole runtime. Call it whatever you want, it's a bug that hasn't been fixed since the 1.0 Framework. The stranger thing about it, is that I have never been able to reproduce it with a simple test. For example, this does not break.

    while(true)
    {
    SqlConnection con = GetMyConnection();
    using(con)
    {
    // run some query

    Doesn't ever throw an exception. Still doesn't even if I run something similar with multiple threads. Using does seem to do what it is supposed to. That's the one thing I don't understand is that if "using" doesn't work, then we should be able to reproduce the problem.

    Tuesday, December 06, 2011 6:54 PM
  • I had the same problem - I was not able to replicate the problem.

    I don't like that - It feels too much like guessing.

    I was eventually able to replace the problem by reducing the "Max Pool Size" entry in the connection string to a really low number (e.g. 2).

    (see http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx for details for how to do this)

    Once I had done that, the problem was easily reproducible, even with only a single thread.

    As William Vaughn suggests (very strongly), explicitly closing the connection (with a try...finally) completely eliminated the issue.

     



    • Edited by ncook Tuesday, December 13, 2011 1:46 AM
    Tuesday, December 13, 2011 1:44 AM
  • In your code you are doing everything synchronously. Rewrite the code to use threads instead, perhaps 10 with a 1000 requests each and you will see the issue arise.
    Monday, August 20, 2012 7:21 PM
  • I'm not sure if you were referring to my (bpeikes) code, but I did try to do this in multiple threads and was not able to reproduce the error. It appears that there is some scenario where "Using" does not work the same way that calling close explicitly does. If you have code that can reproduce the problem, please post it so we can get closer to the source of the issue.

    What people have proposed is that a using clause does not necessarily work the same as calling close explicitly. People have been able to fix this "problem" by removing "using" and making explicit calls to Close(). Why should it make a difference?

    Monday, August 20, 2012 7:29 PM
  • this should be the answer definetly. thanks for the information. how about when we use datasets and entity framework to do DB connections? when their work is finished, connection is handled by datasets or ef  or do we need to close connections manually?

    "Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."

    Friday, April 19, 2013 8:50 AM
  • This is old, but anyway...

    If you have lots of db threads and a really low max pool size, then "Timeout..." is exactly the message you should be getting. 

    Now, why did an explicit close fix the problem for you(ncook/vaughn)?  Well, if you are doing an explicit close, then you are closing the connection sooner than if you wait for the end of the using block to call Dispose().  Dispose() has a couple lines of code that it executes before calling Close().  You might say that those 2 lines and the overhead of a method call don't take very much time.  In absolute terms, that is true, but if you have a lot of threads running and lots of context switching happening, then those 2 lines will make the difference at some point.  You found that point in your case was when the thread pool was set to 2.  Nice experiment.

    So, you want to keep your connection open for as little time as possible.  But you also want your code to be easily readable. 

    Friday, May 24, 2013 7:09 PM