Solving connections pool problem

  • Thread starter Thread starter ra294
  • Start date Start date
R

ra294

I have an ASP.net application using SQL Server 2000 that every once in a
while I am getting this error:
"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 looked at my code and It seems that I do close every connection that I
open so I don't know why I am getting this error.
1. What is the best way to find where this problem is coming from ?
2. In order to monitor it, is there a way to print the number of free
connections in the pool (for example every time I open a new one) ?
3. Is there a way to force clearing the pool from old connections ?

Thanks for your time

(e-mail address removed)
 
ra294 said:
I have an ASP.net application using SQL Server 2000 that every once in a
while I am getting this error:
"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 looked at my code and It seems that I do close every connection that I
open so I don't know why I am getting this error.
1. What is the best way to find where this problem is coming from ?
Click to expand...
2. In order to monitor it, is there a way to print the number of free
connections in the pool (for example every time I open a new one) ?
Click to expand...

Hmm, you might check them using Enterprise Manager (Management/Current
Activity) or using a stored procedure sp_who.
3. Is there a way to force clearing the pool from old connections ?
Click to expand...

Not that I know of. However you might instruct it (via connection string) on
how many connections to pool, etc. See
Connection Pooling for the .NET Framework Data Provider for SQL Server
..net help topic for more details.
 
My first thought on this is that you need to use Enterprise Services (COM+)
write you data access layer in c# as service component. use a mix of JITA
and Object Pooling.
That should fix you up.

The problem is that .net depends on GC to clean up and at times even though
you are have already closed the object its may be still in memory and
waiting to GC'd
With JITA all the clients have is a proxy. If you have Object pooling
enabled on serviced components then they are returned to object pool instead
of being destroyed.
So you can have optimal performance.

Hope this helped,

Hermit Dave
 
Hi Hermit,

Hermit Dave said:
My first thought on this is that you need to use Enterprise Services (COM+)
write you data access layer in c# as service component. use a mix of JITA
and Object Pooling.
That should fix you up.
Click to expand...

Wow. Isn't a bit of an overkill?
The problem is that .net depends on GC to clean up and at times even though
you are have already closed the object its may be still in memory and
waiting to GC'd
Click to expand...

Yes, the objects are still there. However, when you Close connection
explicitly it is released and returned to pool.
 
Overkill... probably yes.
But as he said before... he has closed all the connections and so in his
case... he either needs to increase the connection pool.or manage the db
layer objects more efficiently.
One of the good things bout COM+ is that it is better than .NET as such at
doing instance management. So it might be an overkill but it depends on how
responsive you want your app to be.

Regards,

Hermit Dave

PS: Just finished reading a book on COM+ and .NET... so its bound to stay
for a few days.
 
We have seen this question/quandary before and invariably we've found that
the connections were not getting closed while a handle to the connection was
still available. I would scour your code again looking for places where a
DataReader was passed out of a Function but the receiving class did not
close the DataReader (assuming you had CommandBehavior.CloseConnection set).

Okay, let's assume you ARE closing all of the connections. Another
possibility is that the connections are being held open so long that the
incoming ASP requests are overloading the system so new requests have to use
a new connection (instead of inheriting a dormant connection). This is
caused by slow queries, poor indexing, improper query design or a lack of
RAM (among other things) on the server.

As far as monitoring the pool, if you're using SqlClient, you can try to use
the Performance Counters generated by this provider using PerfMon.

No, going to COM+ won't solve anything. It uses the pool too...

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
you are failing to close a connection (maybe on an error condition). tricks
to finding the cause

1) do a
dbcc inputbuffer (spid)
on connections to see a common pending command

2) set the max pool size to 1 (or whatever max concurrent connections on 1
page is), so that you will error out closer to the failed close.

-- bruce (sqlwork.com)
 
I have to side with Bruce and William

You're not closing a connection somewhere. Check your code and you'll see.
 
There are times in which it is not easy to tell that you are not closing a
connection. Here are the top two offenders:

1)You place connection close or dispose on a class finalizer. Never ever do
this, in fact never touch any managed objects in a finalizer, see my
comments in the docs:
http://msdn.microsoft.com/library/d...datasqlclientsqlconnectionclassclosetopic.asp
CAUTION Do not call Close or Dispose on a Connection, a DataReader, or any
other managed object in the Finalize method of your class.


2)You get an exception in your code that bypasses connection close:
con.Open()
command.Execute //Throws exception
con.Close() //does not get called, ouch!

To fix this problem always use a try finally or the using construct.
try
con.Open()
command.Execute //throws exception
finally
con.Close() //finally is guaranteed even after exception.

or the equivalent:

using (con)
con.Open()
command.Execute //throws exception
con.Close() //Close does not get called, but the using construct
guarantees a call to con.Dispose() so you are ok.

Hope this helps,
 
Back
Top