max pool size reached on dev workstation

[Timo]

In the course of debugging applications on my development workstation
(ASP.NET 1.1.4322, SQL Server 2000, ADO.NET) the app will sometimes be
unable to establish a database connection when I choose Debug->Start. (When
stopped during debugging, the code does not always reach the Finally {}
block where the connections typically get closed.) I get a timeout error
indicating that max pool size may have been reached. Stopping and restarting
SQL Server does not clear this problem, and so I have been rebooting. Is
there another service that maintains the connection pool, which can be
bounced to free the stuck connections?
Thanks
Timo

 

[Fredrik Wahlgren]

In the course of debugging applications on my development workstation
(ASP.NET 1.1.4322, SQL Server 2000, ADO.NET) the app will sometimes be
unable to establish a database connection when I choose Debug->Start. (When
stopped during debugging, the code does not always reach the Finally {}
block where the connections typically get closed.) I get a timeout error
indicating that max pool size may have been reached. Stopping and restarting
SQL Server does not clear this problem, and so I have been rebooting. Is
there another service that maintains the connection pool, which can be
bounced to free the stuck connections?
Thanks
Timo

http://www.error-bank.com/microsoft.public.dotnet.framework.adonet/69274_Thread.aspx

/Fredrik

 

[Sahil Malik]

You need to be calling Dispose on SqlConnection objects yourself soon as you
are done using them. Don't rely on Finally for cleanup.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

 

[Timo]

Sahil,
This is not the "best practices" I've seen offered elsewhere. If there's an
error in the code after opening the connection, execution will jump over the
lines where the code closes the connection, to the catch() block. The only
guaranteed place to close the connection (at runtime, not in debugging mode)
is in the Finally() block, right?
Timo

 

[Timo]

Thanks, Fredrik. I will try Yahnong Huang's suggestions, to turn off SQL
Debugging and/or raise the pool size, though I see that VS.Net may be
opening a lot more connections that I need, according to "bbeny".
Timo

 

[Sahil Malik]

Timo,

I confused the Finally block in a try-catch-finally .. with the Finalize ..
method. My bad !! :-(

Anyway, reading your question again - this time not on drugs -

The connection pool is maintained I think on another thread. There is a good
description of how it works over here -
http://codebetter.com/blogs/sahil.malik/archive/2004/11/12/31798.aspx . If
there is a service you could restart to flush the connection pool - well I
haven't heard of it, but a softie might be able to comment better on that.

Now in your scenario - needless to say, you are leaving too many connections
open - which is why the pool size is going through the roof. You have to
look at your database layer design and general application design to ensure
that connections are being closed.

Now, in your description, you mention that you are using Finally blocks to
close connections. For simplistic cases, that is a good approach, but for
larger application architectures, you should abstract that out using a
DataLayer - thus the Finally that encloses a SqlConnection.Dispose, occurs
inside that one single class, and you always rely on that class to do this
job for you.

There might be situations where this might not work - like transactional
scenarios, or datareader scenarios - in those cases too, you should rely on
a class that sits on top of that class, to close out an open connection - in
other words, your business layer never calls SqlConnection.Close

You might already be doing all this - but the only way to hit the max pool
size is - connections are not being closed as you had expected them to be.
That is the only explanation.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

 

[Timo]

Sahil,
The discussion on the codebetter.com page referred to below would is
somehwat ambiguous. Can you please clarify? You wrote:

"Either close, or dispose (by virtue of calling close)..."

The words: "dispose (by virtue of calling close)" -- suggest that the
connection instance will be disposed as an after-effect of a close. Then
you write:

" All it does is - as stated above - releases resources - which is simply
calling close and a bit else as shown below in
SqlConnection.Dispose(Boolean) code ---

The extra work other than simply closing that dispose did above was..."


This suggests an opposite approach, that the dispose first does a close and
then some additional cleanup. So, do I understand you correctly when I
restate the choices as follows?

To close a connection, pooled or unpooled, the programmer has two choices:

a) invoke the connection's close() method. This will close the connection
and the GC will eventually get around to cleaning up the resources held by
the internal sealed class; or

b) invoke only the dispose() method. This method will close the connection
for you and will have the added benefit of freeing up the internal sealed
class's resources somewhat sooner.

Thanks
Timo