Multiple connections from IIS on 1433 causing problems

I have SQL2000 with 8 databases running on the LAN. In our DMZ we have
a web server [IIS5] which connects to the SQL on 1433 through the
DMZ/LAN port. Up until last Tuesday everything seemed OK with approx 10
connections form DMZ>>LAN on port 1433 whenever the site was opened.
However now I am seeing 50-60 connections even though there are only a
max of 10 users connected.
As a test I closed the website to the WAN [0 connections] and opened
one occurrence of the website . It immediately, created 2 IIS
connections and 10 SQL connections. After 20-30 seconds the SQL dropped
to 2 connections until I clicked an internal link, & then it rose back
to 10 again.
Does this make sense, I dont know what the developers may have done to
trigger this and not sure where to go next. Connection pooling is
enabled & we use DSNs to make the IIS>>>SQL connections.
Thanx for your time
G> Does this make sense, I dont know what the developers may have done to
> trigger this and not sure where to go next. Connection pooling is
> enabled & we use DSNs to make the IIS>>>SQL connections.
Basically, the high-water mark should reflect the number of concurrent open
connections needed by the application. Keep in mind that when concurrent
queries are executed, each requires a separate connection and ADO will
dynamically create additional connection objects as needed. You might try a
Profiler Trace including Batch Starting, RPC Starting, Batch Completed and
RPC Completed events to help identify the cause.
Hope this helps.
Dan Guzman
SQL Server MVP
"gstar" <gary.brett@.gmail.com> wrote in message
news:1168856615.584379.217420@.l53g2000cwa.googlegroups.com...
>I have SQL2000 with 8 databases running on the LAN. In our DMZ we have
> a web server [IIS5] which connects to the SQL on 1433 through the
> DMZ/LAN port. Up until last Tuesday everything seemed OK with approx 10
> connections form DMZ>>LAN on port 1433 whenever the site was opened.
> However now I am seeing 50-60 connections even though there are only a
> max of 10 users connected.
> As a test I closed the website to the WAN [0 connections] and opened
> one occurrence of the website . It immediately, created 2 IIS
> connections and 10 SQL connections. After 20-30 seconds the SQL dropped
> to 2 connections until I clicked an internal link, & then it rose back
> to 10 again.
> Does this make sense, I dont know what the developers may have done to
> trigger this and not sure where to go next. Connection pooling is
> enabled & we use DSNs to make the IIS>>>SQL connections.
> Thanx for your time
> G
>|||I have see this problem before. If you are running IIS, I am assuming
asp or aspx (.net). If you have more connections than users, it is
probably that the application is not closing connections properly. In
"classic ASP" you had a command to open the connection - often at the
top of the web page, ran your query and then closed it at the bottom of
the page as the code was interpreted. Maybe this will help give the
developers some ideas. Same sort of concept can happen with .NET.
KR
Dan Guzman wrote:[vbcol=seagreen]
> Basically, the high-water mark should reflect the number of concurrent ope
n
> connections needed by the application. Keep in mind that when concurrent
> queries are executed, each requires a separate connection and ADO will
> dynamically create additional connection objects as needed. You might try
a
> Profiler Trace including Batch Starting, RPC Starting, Batch Completed and
> RPC Completed events to help identify the cause.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "gstar" <gary.brett@.gmail.com> wrote in message
> news:1168856615.584379.217420@.l53g2000cwa.googlegroups.com...