none
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

    Question

  • Let me explain my app scenario for better and clear understanding - 

    1. I had a .NET 2.0 Web Service hosted on a Windows 2008 Server, connecting to SQL 2005 in the backend. Everything was working fine.
    2. Today, I upgraded the above project to .NET 4.5 and hosted it under Windows 2012 R2 boxes, with SQL Server 2016 in the back end.

    Everything got updated and no major code modifications(.NET Upgrade) was done and it worked fine.

    When I deployed my services to PROD, i started seeing this error "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" and my application got flagged. The servers which has 2.0 on it, still works like a charm. The problem is only on my new servers.

    This looks like a connection pool issue.

    Can anyone please let me know what should I do to fix this issue? Is there any additional IIS setting that I am missing? 

    Wednesday, May 3, 2017 7:40 PM

Answers

  • I got the problem. The problem was that one of my method was just opening connections and closing it.

    Closing the connections in finally solved the problem. I tried replicating the same problem on .NET 2.0 server and things worked smoothly there without the connection close code. 

    With 4.5 things are working only with connection closing code.

    Wednesday, May 31, 2017 2:25 AM

All replies

  • https://social.msdn.microsoft.com/Forums/sqlserver/en-us/home?forum=sqldatabaseengine

    What you can do is post to the SQL server forum and have them show you the commands to expose the application that is using all the connections in the connection pool.

    Wednesday, May 3, 2017 8:20 PM
  • Let me explain my app scenario for better and clear understanding - 

    1. I had a .NET 2.0 Web Service hosted on a Windows 2008 Server, connecting to SQL 2005 in the backend. Everything was working fine.
    2. Today, I upgraded the above project to .NET 4.5 and hosted it under Windows 2012 R2 boxes, with SQL Server 2016 in the back end.

    Everything got updated and no major code modifications(.NET Upgrade) was done and it worked fine.

    When I deployed my services to PROD, i started seeing this error "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" and my application got flagged. The servers which has 2.0 on it, still works like a charm. The problem is only on my new servers.

    This looks like a connection pool issue.

    Can anyone please let me know what should I do to fix this issue? Is there any additional IIS setting that I am missing? 


    Probably a coding issue on your part. Typical cause for this phenomenon is allocating resources and forgetting to release them. Are you closing SQL Connections properly, for example?
    Wednesday, May 3, 2017 8:37 PM
  • >Are you closing SQL Connections properly, for example?

    Yes.  This is typically caused by failing to close SqlConnection objects and letting them "leak" onto the managed heap.  Once there only they will not be closed until their finalizers run, which may be in time to prevent a failure, and may not.

    And it's not too surprising that it only failed in production, and on .NET 4.5, as finalization is non-deterministic.  It would happen on .NET 2.0 as well; you've just been lucky.

    David


    Microsoft Technology Center - Dallas
    My blog

    Wednesday, May 3, 2017 9:09 PM
  • Thanks for the replies.

    I too think there is a code problem. But the thing is how things are working fine on the OLD server with .NET 2.0. With 4.5 things should get better and might have changed. 

    From the DB stand point, when I connected with my DBAs we came to know that from the new 2012/4.5 servers 800 connections were opened and was in sleeping state. Whereas, from the OLD server its hardly 20/30.

    Also to note - The major difference here are 
    .NET 2.0 to .NET 4.5
    SQL 2005 to SQL 2016
    Win 2008 to Win 2012 R2


    Thursday, May 4, 2017 5:48 PM
  • Actually the code is too OLD and apart from changing the .NET framework, no other changes are made to the way connections are done. If I had to change something, it will be a lot and that wont be advisable now.

    Hence, I need something very minimal and if not in code something in IIS, it will be great.

     
    Thursday, May 4, 2017 5:53 PM
  • Done 

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3dff93bd-9b67-46ef-8ced-43f183a2a697/connection-pool-between-sql-2005-and-sql-2016-database?forum=sqldatabaseengine

    Thursday, May 4, 2017 5:54 PM
  • >Hence, I need something very minimal and if not in code something in IIS, it will be great.

    You can always turn off connection pooling, or expand the connection pool size.  These settings should both be in your connection strings in the web.config.

    SqlConnection.ConnectionString

    And this is a C# and .NET issue.  This is the right forum.

    David


    Microsoft Technology Center - Dallas
    My blog


    Thursday, May 4, 2017 6:04 PM
  • Default connection pool looks to be 100. Was there any major code changes in .Net 2.0 and .NET 4.5 related to this ?
    Thursday, May 4, 2017 7:15 PM
  • Default connection pool looks to be 100. Was there any major code changes in .Net 2.0 and .NET 4.5 related to this ?

    Are you even sure you are even using connection pooling properly, by using a generic user id and psw for all connections your application uses?
    Thursday, May 4, 2017 8:28 PM
  • >Default connection pool looks to be 100. Was there any major code changes in .Net 2.0 and .NET 4.5 related to this ?

    Nope.  This is just the Garbage Collector leaving the leaked connections on the managed heap long enough to expose the bug in your code.

    David


    Microsoft Technology Center - Dallas
    My blog

    Thursday, May 4, 2017 8:28 PM
  • My main confusion is why things are working on .NET 2.0 and not .NET 4.5

    Since, it was production i had to make things works with new DB, i have switched SQL 2016 connection string in my .NET 2.0 code on 2008 server. It still works fine.

    So, now its .net 4.5 and 2012 server with all default IIS settings.

    Thursday, May 4, 2017 9:01 PM
  • >My main confusion is why things are working on .NET 2.0 and not .NET 4.5

    A bug in your code causes a failure if more that X SqlConnections are opened between Garbage Collection cycles.  It might have failed on .NET 2.0; you just got lucky and GC happened frequently enough to prevent the failure. 

    But Garbage Collection is non-deterministic, and the garbage collection behavior changed moving from 2.0 to 4.0.  It just so happens GC is happening less frequently, or perhaps more frequently and more leaked connections are being promoted to Gen2, where GC is infrequent.  In any case you are getting 100 leaked SqlConnections on the managed heap, preventing any new connections from being opened.

    David


    Microsoft Technology Center - Dallas
    My blog

    Thursday, May 4, 2017 9:37 PM
  • I dont agree to that term LUCKY, bcoz the code has been on .NET 2.0 from last 7 years and there was never such issues logged or reported. This only got raised with the upgrade.

    Hence, I need to look into the differences between 2.0/4.5.

    Saturday, May 6, 2017 1:13 AM
  • > Hence, I need to look into the differences between 2.0/4.5.

    The Garbage Collector was significantly changed between 2.0 and 4.5.

    David


    Microsoft Technology Center - Dallas
    My blog

    Saturday, May 6, 2017 1:25 PM
  • Thanks for this information.

    I will now look into GC related information.

    Saturday, May 6, 2017 8:39 PM
  • I got the problem. The problem was that one of my method was just opening connections and closing it.

    Closing the connections in finally solved the problem. I tried replicating the same problem on .NET 2.0 server and things worked smoothly there without the connection close code. 

    With 4.5 things are working only with connection closing code.

    Wednesday, May 31, 2017 2:25 AM