"William (Bill) Vaughn" wrote:

> Ok, so if it's a server issue, we need to look at what's bogging down the
> server. Is it hosing Reporting Services or acting as a print server? If the
> server bogs down, the ASP clients have to wait and their pool(s) grow
> accordingly as new requests come in. Eventually, the server crowbars and
> won't accept any more connections due to the fact it has no CPU cycles to
> launch another thread--even for SSMS. I would at least open up PerfMon to
> monitor what's going on AFA the CPU use and other critical counters such as
> logical connections etc. The connection pools are not on the server unless
> IIS (which is hosting the ASP applications) is there too. In that case, the
> application code itself might be bogging down the host system.
>
> --
> ____________________________________
> 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@discussions.microsoft.com> wrote in message
> news:8E8970C6-84BB-4B05-AB71-A7E4381147EB@microsoft.com...
> > Thank you for the follow up. As I had indicated the client does start to
> > fail
> > to contact SQL Server but alll clients will stop at the same time
> > indicating
> > a server side issue not a client side issue. SQL Server prevents new
> > connecitons being created. SQL Server allows unlimited connections. I can
> > understand a client filling its connection pool but why do all clients,
> > anywhere, including SQL Management Studio all fail to connect to SQL
> > Server.
> >
> > The SQL Server perf counters themselves show only 300 logical connections.
> >
> > Regards
> >
> > James
> >
> > "William (Bill) Vaughn" wrote:
> >
> >> 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@discussions.microsoft.com> wrote in message
> >> news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@microsoft.com...
> >> >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.