The timeout period elapsed prior to obtaining a connection from the pool.

Question

I'm getting this error every few days. I won't see the error for a few days then I'll get a flurry of 20 or so all with in a minute or so.

I've been very thorough going throw my code so that I'm using this basic setup for my DB access.

try
{
  myConnection.Open();
  mySqlDataAdapter.Fill(myDataTable);
  myConnection.Close();
}
Catch (Exception err)
{
  if (myConnection.State != ConnectionState.Closed) myConnection.Close();
  throw err;
}

The way I understand it this should execute my queries and immediately release the connection back to the pool but if something goes wrong with the query then I catch the excpetion close my connection then throw the error up, which eventually gets trapped at the application level and logs and emails me the error.

Even using this throughout my code I'm still running across the issue. What can I do to diagnose the root cause of the issue?

Answer 1

changing the code to something like this makes it easier to read..

try
{
  myConnection.Open();
  mySqlDataAdapter.Fill(myDataTable);
 }
Catch (Exception err)
{
  throw err;
}
finally
{
 myConnection.Close();
}

But it doesn't help your timeout..

It sound like the fill statement takes to long. Or that the problem actually is somewhere else, where you don't the connection. SQL Profiling could help figuring out if the select statement takes to long..

Answer 2

The issue is the number of pooled connections you can have in the pool.

In your connection string, you can add the "Max Pool Size=100" attribute to increase the size of your pool. However it sounds like you are concurrently running a significant number of SQL queries, all of which are long running. Perhaps you should look at ways to either shorten the queries or run them sequentially through a single connection.

Answer 3

I was just using that fill statement of an example of how I've got my connection.open(), connection.close() statements wrapped in error handling.

This is happening on a fairly sizable website, we get about 120,000 pageviews a day. So we've got a whole bunch of different things that are connected to the DB, product searches, user accounts, order histories, ect...

I know there is probably just a single querery or two that are causing this problem. I'm just trying to hunt them down.

Answer 4

If you're using MSSQL set up a profile running for sometime a day or two.. Make the profile to be saved to a file or a table, file is supposed to be faster...

And then having a script reading that file to a table you could easily query it to find the longest running queries.

Answer 5

A quick question here. Are you by chance on an access DB because there is a limit on the number of connections that you can concurrently have on it which would result in your type of error. SQL serve shouldn't have the same problem.

if you are runing SQL server then turn off connection pooling and see if it makes a difference to your app.

I found out with our system about 5 years ago when our company was rapidly growing that we basically broke access when we started constantly hitting the user cap. We switched to SQL in about 24 hours and haven't had a problem since.