Connection Pool Time Out

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have the following scenario:
App1: Asp.Net Application that shows reports from some SQL DB
App2: Asp.Net WebService with two services exposed for clients, and a single
“Checker†in the Application object that checks every some time (normally 2
or 3 minutes) for conditions in the same DB, and insert some values in it
using a transaction.

When the time spans completes, the checker retrieves via a SqlDataReader the
ready items and make the insertions as shown:

SqlDataReader reader = GetReadies();

try
{
// do something

// initialize the transaction “trans†and the connection “conâ€

try
{
// insert using the transaction

trans.Commit();
}
catch(Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
trans.Dispose();
con.Close();
}
}
finally
{
reader.Close();
}

In the web application I have few pages that get information from the DB,
and after a few retrievals, I got 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.â€

So I review all code searching for unclosed datareaders or connections, for
nothing. I use SQLHelper in the web application and I always retrieve
datasets, anyway, I checked that all connections were closed.

Then I review the code in the webservice app, I think it is correct, isn’t it?

Even though I didn’t found unclosed connections, I started to watch the .Net
CLR Data performance object with the object Current # pooled connections. The
graph never goes over 7 connections; it oscillates between 5 and 8. I also
started a new trace in the SQLProfile to see if it audits logout events
occurs.

And still, after a while browsing in the asp.net application, I receive the
same error.

Note: the max pool size is set to 100 explicitly.

Any idea of the cause?

Thank you very much.
 
Try flagging the connection as disposed, to ensure it is released to the
pool in a timely manner. You do not need a separate close if you do that.

I would also run through the code in its entirety and see if you have
anything in a potentially wrong order. If the reader is a DataReader, that
is one area where there is a potential problem, as you close con first, but
I would have to see the entire code.

I am not sure if ohter forms of timeout can be sent as a connection pool
timeout, but it is possible given the history of MS.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
 
Thanks for your response. I call the Dispose method instead of the Close, I
don´t receive the error always, but still happening.

Thank you
 
Not sure :(

Any good place to read something objetive about that?

Thank you for your response.
 
Back
Top