none
Connection pool timeout errors

    Question

  • We have a third party application the routinely shows the following error during our end of day procedures.

    "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've ben reading up on connection pools and from what I've been able to gather is that the max pool size is determine by the application in the connection string properties. We have several applications that use the .net sql data provider to make connections to our sql server. I dont quite understand whether or not multiple connections from multiple applications are capable of using the same connection pool. What I would like to know is whether or not the pool is per application or is the pool for all applications? Could two applications combined cause the pool size to go over the default of 100 and present this timeout error? Or does each application/process have its own pool with a limit of 100?

    I'm trying to narrow down whether or not the application that presents the error, is the application causing the error.

    Any input is greatly appreciated.

    RKB

    • Moved by Bob BeaucheminMVP Saturday, October 08, 2011 2:40 AM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Friday, October 07, 2011 9:55 PM

Answers

  • If two different applications use the same connection string they will share the same pool, correct.   Your best bet at this point is to contact the vendor and complain about the pooling leaks.
    Matt
    Tuesday, October 11, 2011 4:48 PM
    Moderator

All replies

  • The pool is per each unique connection string (not across all connections).  So for example if you have these two connection strings, they would go to different pools:

    Server=MyServer;Database=Db1;Integrated Security=SSPI;  // Connection to Db1 database

    Server=MyServer;Database=Db2;Integrated Security=SSPI;  // Connection to Db2 database goes to different pool.

    Since I did not specify Max Pool Size, I get the default of 100 connections per pool.   If the application attempts to open more than 100 at a time, they get the "The timeout period elapsed prior to obtaining a connection from the pool" error.

    Usually this error is caused by "leaking" connections.   You can leak a connection by not closing it when you are finished with it.  If you do not close the connection, then it goes to the garbage collector and "eventually" it gets closed but "eventually" may take a long time to occur.

    To solve this problem I use the following technique which is very successful:

    1. Locate connection strings in the application and "partition" them in some fashion.  For example if your connection string is stored in a configuration file, change the configuration file to now have 10 separate connection strings (Connection1 through Connection10) instead of 1.

    2. For each connection string, change the Application Name property to "ConnectionX"  when X is the number 1 through 10.

    Example connection string:

    Server=MyServer;Database=Db2;Integrated Security=SSPI;Application Name=Connection9;"

    This leverages the principle that "each unique connection string results in a unique pool".   Now we have 10 pools instead of just 1.

    3. Modify the code to pick one of the pools 1 through 10 evenly throughout your code files (so 10% use Connection1, 10% use Connection2, etc..)

    4. Run the code, eventually you will see some of the pools work ok and some of them (or maybe just one) is still having problems.  This is how you narrow down where the code is that is "leaking" the connections without having to review all of the code which may take a long time.

    You can use another more advanced variation of above by writing a single global static function to return a connection and require people to use this function to open connections.  The function requires a tag parameter to apply to their connection (the tag you just set to Application Name to partition the pool).   The tag could be the file name or the programmer for example and this would help quickly narrow down the offending code.


    Matt
    Sunday, October 09, 2011 6:58 PM
    Moderator
  • Hi Matt, thanks for the reply. Unfortunately the application is provided to us and we can not view or edit the code that runs it. I've long suspected the application is leaking connections.

    What if two different applications use the same exact connection string. Will they share the same connection pool?

    Monday, October 10, 2011 1:16 PM
  • If two different applications use the same connection string they will share the same pool, correct.   Your best bet at this point is to contact the vendor and complain about the pooling leaks.
    Matt
    Tuesday, October 11, 2011 4:48 PM
    Moderator