Why do I sometimes fail to get a db connection?

Question

On a production system, I occasionally find the following error in the log:

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.

In order to remedy this I increased the maximum pool size to an outrageously high 10,000:

connectionString="metadata=res:///MyEntities.csdl|res:///MyEntities.ssdl|res://*/MyEntities.msl;provider=System.Data.SqlClient;provider connection string="data source=localhost;initial catalog=MyDb;integrated security=True;MultipleActiveResultSets=True;Max Pool Size=10000;App=My Service""

But the problem still occurs. What other causes could there be to this error, other than the connection pool being maxed out?

EDIT: before anyone else suggests it, I do always use using(...) { } blocks whenever I open a connection to the DB, e.g.:

using (var db = new MyEntities())
{
   // do stuff
}

Have you made absolutely sure that you have closed the connection to the database every time you have opened it? — Andrew Morton, Jun 10 '14 at 21:19
@Shaul are you making many connections to the DB in parallel, and are you using `using` on connections or only on the commands and the DataReaders? Show some example code in your question showing how you handle your connections. Even if you are using `using` statements correctly, if you are doing things in parallel you could have threads that get blocked before the using statement completes and never returns. That could easily cause you to run out of connections in the pool. — Scott Chamberlain, Jun 10 '14 at 22:33
@ScottChamberlain yes, there are lots of connections happening in parallel. `Using` statements as per edit to question. Does this help? — Shaul Behr, Jun 10 '14 at 23:32
Get a count of the number of Threads you have next time the error happens (unless you are using Async/Await). Do you have any code that would block a thread inside the `using` statement? Or can you show your code that creates your parallel instances. — Scott Chamberlain, Jun 10 '14 at 23:36
You could look at it from the SQL Server side with [Activity Monitor](http://technet.microsoft.com/en-us/library/ms191199%28v=sql.105%29.aspx) in SSMS or using [sp_who](http://stackoverflow.com/questions/1248423/how-to-see-active-sql-server-connections). — Andrew Morton, Jun 11 '14 at 20:04

score 0 · Answer 1 · answered Jun 10 '14 at 21:20

How are you connecting to the database?

Having a larger number of connections will make your application live longer, but it's likely that the root problem is that you're not releasing all of your connections properly. Check that you are closing connections after opening them. e.g.

using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    ... perform database query
}

will automatically close the connection when done.

I always use `using` blocks. – Shaul Behr Jun 10 '14 at 22:18 — Shaul Behr, Jun 10 '14 at 22:18

Md Nazmoon Noor · Answer 2 · 2014-06-10T22:25:29.110

0

Always .Close() the connections. That means always doing it is very important practice. If you're not doing it, you are doing it wrong. Any application can overfill the ConnectionPool. So make sure you have invoke the .Close() each time you opened it to clear the pool. Do not depend on the GC to close the connections.

Make sure you call .Close() in try blocks, also in catch blocks.

edited Jun 10 '14 at 22:25

answered Jun 10 '14 at 21:20

Md Nazmoon Noor

3,187
1
24
30

"Make sure you call .Close() in try blocks, also in catch blocks." - you could do that in the `finally` section, in lieu of using `using`. – Andrew Morton Jun 10 '14 at 21:24
1

I always use `using (...) {}` blocks. This would achieve the same thing, right? – Shaul Behr Jun 10 '14 at 22:17
Yes it would. The implementation of `DbConnection.Dispose()` invokes `Close()` as well. – Md Nazmoon Noor Jun 10 '14 at 22:23

score 0 · Answer 3 · answered Jun 10 '14 at 21:39

Usually this happens because you in the code close, for example, a DataReader, but you do not close its associated connection. In the code above, there are two solutions depending on what you would like to do.

1/ Explicitly close the connection when done.

connection.Close();

2/ Use the connection in a Using block, this guarantees that the system disposes the connection (and closes it) when the code exits the block.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here; connection closed on following line.
}

I always use `using` blocks. – Shaul Behr Jun 10 '14 at 22:18 — Shaul Behr, Jun 10 '14 at 22:18

score -2 · Answer 4 · answered Jun 10 '14 at 23:37

-2

From what I have gathered from other sources like this MSDN page the garbage collection of the using (...) { } block is insufficient to prevent your application from running out of connections. The reply by William Vaughn states that clearing the connection explicitly via the Close() method returns threads to the pool far more quickly than a reliance on garbage collection.

So, while you have done nothing wrong as the using (...) { } block is proper coding, its lack of efficiency is what is leaving threads tied up too long. You may also look into the Collect() method to "force" garbage collection, but as the documentation states, this may cause performance issues (so it might be an option, or it might be trading one problem for another).

answered Jun 10 '14 at 23:37

Omnidisciplinarianist

247
3
7

2

That's plain wrong! When you get out of a `using` block, the `Dispose()` method of the "used" object is called. In the case of `SqlConnection`, the implementation of `Dispose()` includes a call to `Close()` (and some other things, see the source code). Som whenever the code get out of the using block, the connection is automatically closed. The GC will remove the object from memory at any undefined time, but `Dispose`, and thus `Close`are executed when leaving the using block. – JotaBe Jun 11 '14 at 11:02
@JotaBe, I am quoting from an author who writes books about Visual Studio and SQL Server. You may tell me that I'm wrong, but I am simply reporting the findings of a subject matter expert. If Microsoft were perfect and the language always worked as it says it does, we would never ever need to submit bugs. Of course, this is not the case. Is it really so odd that an expert has noticed that the language responds better to one command than another? – Omnidisciplinarianist Jun 24 '14 at 21:55
Please, look at the source code, learn what `using` is and what it has to do with `Dispose` method and GC. When you have a `using` block, `Dispose` is called inmediately after leaving the `using` block, and this implementation of `Dispose` calls `Close`, just as you'd do. Some time later, no matter how much or less, the GC will do its job, which has absolutely nothing to do with closing the connnection (which was closed on the `Dispose` method - that's why `IDisposable` and `using` exist). Please, read again Bill Vaughn text. Isn't he speaking of relying on GC because there is no using block? – JotaBe Jun 25 '14 at 10:28
If you don't have a `using` block, the `Dispose` method will be called by the GC when it decides to get rid of the "orphan" object. In this case you can get into trouble because you don't have any warranty that this will happen soon. This is a thorough explanation of `Dispose`, `Finalize`, GC and using: http://www.codeproject.com/Articles/15360/Implementing-IDisposable-and-the-Dispose-Pattern-P – JotaBe Jun 25 '14 at 10:42

Why do I sometimes fail to get a db connection?

4 Answers4