Re: Connection refusals

The fact that the server is calling out to other code could certainly lead
to client-side connection pool issues as the application threads queue up
waiting for the server to respond. I'm assuming this is an ASP application
where this sort of behavior is most common. Remember if the client (the ASP
app) request is not handled, when another app instance is started it can't
reuse the connection but has to create another. If this continues the pool
can fill quite quickly. I would monitor the pooled connections and see if
this is not the case.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"James Wren" <JamesWren@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@xxxxxxxxxxxxxxxx
I agree that sounds like a probable cause, however there are two issues I
am
not sure about. However in my experience filling the connection pool
affects
the client not the SQL Server.

1. No code has changed at all just the build of SQL Server and the fact it
is SP2 - so this would mean the behaviour of SQL Server w.r.t the code has
changed to cause this issue now.

2. Also there is a large amount of code called from the main website,
internal software and application servers, so I'm not sure it will be very
easy at all to track down a wrongly closed connection.

3. If, say, a rogue web server component did not close connections surely
this would not effect a .NET application connecting to the SQL Server from
another machine.

Thank you for your response.

James Wren


"William (Bill) Vaughn" wrote:

This sounds suspiciously like a full connection pool. Check out my
whitepaper on managing the connection pool on my site.
http://www.betav.com/sql_server_magazine.htm
I would write an application to monitor the pool or simply setup a
performance counter view on the counters exposed by ADO.NET. I expect
that
you've missed a Connection Close in an exception handler.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"James Wren" <JamesWren@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50D6E4FA-173A-4751-A2EC-A9F8549C7356@xxxxxxxxxxxxxxxx
Hi,

We have a 64bit SQL Server 16GB Memory running on Windows 2003 x64.
This
is
one of our main business database servers. At this time we have an
issue
where the server will stop allowing new connections in, the client will
receive a timeout (instantly) and from .NET the stack is

at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

If the client keeps trying eventually they might get in but other
connection
attempts continue to fail. This lasts for about 10 minutes usually then
goes
away.

Occassionally when this happens the .NET AppDomain is unloaded and a
new
one
is created. However this does not happen all the time and appears to be
a
side-effect not the cause.

What I really would like some help on is what performance counters
might
be
worth looking at.

This is an upgrade of a server with 4GB 32bit SQL Server on Win2003 32
bit
which never had these issues. No code has changed, there are 5 CLR
Stored
procedures which have not changed.


Any pointers would be much appreciated.

Thanks

James Wren





.