Connection Pool Time Out

[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.

 

[Cowboy \(Gregory A. Beamer\)]

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!
*************************************************

 

[Jeff Dillon]

You are polling the db? That is unnecessary. Look into SQL event messages

 

[Guest]

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

 

[Guest]

Not sure

Any good place to read something objetive about that?

Thank you for your response.