none
How to fix timeout problem RRS feed

  • Question

  • I had to reinstall Windows 10 and, of course, SQL SERVER EXPRESS.  I accepted all the default options when doing the reinstall.  However, now when I try to run my applications develope4d in VS2019 I get a timeout error:

    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.

    I am sure this is not a pool size problem because the single application that I run only open one connection at a time within a Using block.

    I've read a lot of links about this but none seemed to apply.  Please let me know how to approach this problem

    Saturday, August 3, 2019 1:47 AM

Answers

  • The problem was in my coding.  Within my Using block I had a recursive call to open a new connection.  Unfortunately, I had and error in my recursion with the result that it never reached an end condition.  When I fixed that, the problem described in the OP was no longer.
    • Marked as answer by RogerSSB Tuesday, August 13, 2019 5:11 PM
    Tuesday, August 13, 2019 5:11 PM

All replies

  • Good day Roger,

    This sound like an issue in the application side. 

    The default number of Max Pool Size is 100, the connection timeout is 15 seconds, and locking period is 5 seconds. From the SQL Server side the maximum number of concurrent connections to a single SQL Server instance is 32767.

    You can read about SQL Server and the clients uses the connections Pool and why, in the following doc (highly recommended!):
    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

    In short, your application probably doesn't close its database connections correctly. There iks a chance thta before the re-installation of the system someone configured the size of the pool to be bigger than the default which hided the real issue, or maybe you actually need to use more, which is VERY rare cases... in this case AFTER YOU CONFIRMED THAT THIS IS REALLY NEEDED BY THE APP ARCHITECHTURE, you can change the default value from 100 to 200 for example

    * You can monitor connection from the server side by query sys.dm_exec_sessions


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]
    Saturday, August 3, 2019 7:26 AM
    Moderator
  • Ronen's post is spot on, I just want to emphasise a few things.

    1) The error message comes from the client API, not from SQL Server.

    2) If you monitor sys.dm_exec_connections or sp_who it is likely that you will see the number of connections rise constantly.

    A mistake many programmers make is that they say "I am sure this is not a XXX problem because...", when in fact XXX is exactly the issue. I might have done it myself once or twice. Or even thrice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 3, 2019 8:55 AM
    Moderator
  • I am sure this is not a pool size problem because the single application that I run only open one connection at a time within a Using block.

    I suggest you run a trace (Profiler or Extended Events) capturing login and logout events. If you see multiple login events without a corresponding logout, that confirms pooled connections are not being reused as expected, exhausting the client connection pool. I wouldn't expect that if this is a single-threaded application with connections wrapped in a using block. However, it can occur with multiple threads and/or async methods that result more connections than expected due to long-running queries. The solution in that case would be query and index tuning or limiting the number of concurrent queries.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Saturday, August 3, 2019 1:07 PM
    Moderator
  • The problem was in my coding.  Within my Using block I had a recursive call to open a new connection.  Unfortunately, I had and error in my recursion with the result that it never reached an end condition.  When I fixed that, the problem described in the OP was no longer.
    • Marked as answer by RogerSSB Tuesday, August 13, 2019 5:11 PM
    Tuesday, August 13, 2019 5:11 PM