2

I am getting the below error from my .net application.

Source: xxx.Services.xxx.xxx.xxxx.xxxx.xxxxRequest
    ---
    System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: 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 have seen several posts regarding this connection pool error.I have gone through most of it and i understand why it is happening and the possible solutions from on the application side like closing the connections opened by application. These is what i understood :

  1. Opened connections are not closed, called connection leaking.
  2. Morethan 100 concurrent connections are being used. Increase the max pool size in the connection string if we have more than 100 simultaneous users.
  3. Slow queries or open transactions blocks new connections. When the connections are being held open by slow query execution/open transactions and instead of being reusing connections the new connection are open and eventually reaches the connection pool maximum.

How can i monitor this from the database side.I saw some suggestions like use sp_who or sp_who2 to see the existing sessions.When i execute sp_who2 when i see these errors,i see lots of active sessions,but how can i see the connection pool information and the connections which comes under the pool. Questions:

  1. Is there any way to track the connections opened and relate it to a connection pool created from database side.

I have noticed that when the issue happens,the queries which normally runs faster ,goes slow.

  1. Is it expected?Because i am suspecting whether the slow queries are causing the connection pool errors.
7

Is there any way to track the connections opened and relate it to a connection pool created from database side.

Set the Application Name in your config and you can monitor from the server side in sys.dm_exec_sessions. You'll get a separate Connection Pool for each Connection String, so if you set the Application Name in the connection strings you can tell what pool each connection belongs to.

the queries which normally runs faster ,goes slow. Is it expected?

Yes. Before you start seeing timeouts getting connections, you will have waits of up to 30 seconds waiting for a pooled connection to become available. So it may be a delay in opening or re-opening a connection, rather than a slow query.

Or it could be an indirect correlation. Often the code path that leaks connections only happens when you get some other Exception. For instance blocking or resource exhaustion on the database is causing timeout Exceptions, which, due not using using blocks is causing the leaked connections. But that's just speculation.

BTW 99.99% of the time the cause is: "Opened connections are not closed, called connection leaking."

  • Appreciate the response.I don't know if you have noticed source: xxx.Services.xxx.xxx.xxxx.xxxx.xxxxRequest.It is different sometimes and that indicates different operations on application.I get that when a new connection request comes,it waits for a pooled connection to become available,if now it timesout giving the error.So i have this question why does the existing connection which does some operation slows down because of this? – user9516827 Oct 2 '18 at 18:00
  • 1
    You should be able to avoid this issue almost always by properly using the using directive - which means your code doesn't have to actively close / dispose of the connection, it is always returned to the connection pool, even on error. – Aaron Bertrand Oct 2 '18 at 18:47
  • @user9516827 see edited answer for additional information about possible causes of the slowness. – David Browne - Microsoft Oct 2 '18 at 22:55
  • The reason i am worried about this is because,the application has been working for years and after migration to SQL server 2016,we started getting this connection pool error.Also i believe that disposing connection with 'using' is something which won't be overlooked by developer.So i suspect some other causes like any slow running queries. – user9516827 Oct 3 '18 at 15:59
  • It's not at all uncommon for connection leaks to go undiagnosed for a long time. So long as the Garbage Collector keeps up with the leak rate, then you'll never see an Exception. Look for other errors happening in the workload, turn on the Query Store to track query execution runtime. – David Browne - Microsoft Oct 3 '18 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.