Solving connections pool problem

[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

ra***@hotmail.com

[Miha Markic]

"ra294" <ra***@hotmail.com> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...

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) ?
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 ?

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.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

[Hermit Dave]

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
"ra294" <ra***@hotmail.com> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...

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

ra***@hotmail.com

[Miha Markic]

Hi Hermit,

"Hermit Dave" <he************@CAPS.AND.DOTS.hotmail.com> wrote in message
news:O$**************@TK2MSFTNGP12.phx.gbl...

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

Yes, the objects are still there. However, when you Close connection
explicitly it is released and returned to pool.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

[Hermit Dave]

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.
"Miha Markic" <miha at rthand com> wrote in message
news:eW****************@TK2MSFTNGP11.phx.gbl...

Hi Hermit,

"Hermit Dave" <he************@CAPS.AND.DOTS.hotmail.com> wrote in message
news:O$**************@TK2MSFTNGP12.phx.gbl...

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.

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

Yes, the objects are still there. However, when you Close connection
explicitly it is released and returned to pool.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

[William \(Bill\) Vaughn]

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

"ra294" <ra***@hotmail.com> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...

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

ra***@hotmail.com

[bruce barker]

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)
"ra294" <ra***@hotmail.com> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...

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

ra***@hotmail.com

[Eric Newton]

I have to side with Bruce and William

You're not closing a connection somewhere. Check your code and you'll see.

--
Eric Newton
er**@ensoft-software.com.cc (Remove the CC)
www.ensoft-software.com
C#/ASP.net Solutions developer

"bruce barker" <no***********@safeco.com> wrote in message
news:uX****************@TK2MSFTNGP10.phx.gbl...

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)
"ra294" <ra***@hotmail.com> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...

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

ra***@hotmail.com

[Angel Saenz-Badillos[MS]]

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/de...closetopic.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,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

"Eric Newton" <er**********@hotmail.com> wrote in message
news:OB**************@TK2MSFTNGP12.phx.gbl...

I have to side with Bruce and William

You're not closing a connection somewhere. Check your code and you'll see.
--
Eric Newton
er**@ensoft-software.com.cc (Remove the CC)
www.ensoft-software.com
C#/ASP.net Solutions developer

"bruce barker" <no***********@safeco.com> wrote in message
news:uX****************@TK2MSFTNGP10.phx.gbl...

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)
"ra294" <ra***@hotmail.com> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...

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

ra***@hotmail.com