Am I hitting the connection limit? DB Connection timeouts in ASP.

We're having occasional timeouts connection to the DB (SQL Server 2K)
from ASP pages (Win2K + IIS 5, but no .Net). Perf Mon on SQL Server
indicates the User Connections sticks around 90 (+/- 5). When freqent
timeouts appear in ASP pages, Query Analyzer, Enterprise Manager and DTS
packages have no problems. Thus I wonder if it's a client side issue,
perhaps related to connection pools under ASP.

A typical connection string in our ASP pages looks like:
"Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=xxx;Password=xxx;Initial Catalog=xxx;Data Source=xxx"

How do I determine what the connection pool size is? Presumably the
default. How do I determine how many connections are in use? Most of
the answers I've seen when searching via Google seem to pertain to
ASP.Net, which doesn't help. We have multiple machines running multiple
web sites, which makes it a little harder to debug from the server side
(or at least, I don't know what to look for). We also have some ISAPI
filters and extensions written in C++ that keep their OLE DB for
SQLServer and OLE DB for ODBC connections open for weeks at a time.
They don't seem to have a problem... but could they add to the ASP issues?

TIA
Malc

Ken
Wed Oct 29 23:33:40 CST 2003

What is the exact timeout error message? A timeout getting a connection? A
timeout executing a command? etc

Cheers
Ken


"Malcolm Ferguson" <Malcolm_Ferguson@NO_SPAM_PLEASEyahoo.com> wrote in
message news:ewplX6pnDHA.1884@TK2MSFTNGP09.phx.gbl...
: We're having occasional timeouts connection to the DB (SQL Server 2K)
: from ASP pages (Win2K + IIS 5, but no .Net). Perf Mon on SQL Server
: indicates the User Connections sticks around 90 (+/- 5). When freqent
: timeouts appear in ASP pages, Query Analyzer, Enterprise Manager and DTS
: packages have no problems. Thus I wonder if it's a client side issue,
: perhaps related to connection pools under ASP.
:
: A typical connection string in our ASP pages looks like:
: "Provider=SQLOLEDB.1;Persist Security Info=True;User
: ID=xxx;Password=xxx;Initial Catalog=xxx;Data Source=xxx"
:
: How do I determine what the connection pool size is? Presumably the
: default. How do I determine how many connections are in use? Most of
: the answers I've seen when searching via Google seem to pertain to
: ASP.Net, which doesn't help. We have multiple machines running multiple
: web sites, which makes it a little harder to debug from the server side
: (or at least, I don't know what to look for). We also have some ISAPI
: filters and extensions written in C++ that keep their OLE DB for
: SQLServer and OLE DB for ODBC connections open for weeks at a time.
: They don't seem to have a problem... but could they add to the ASP issues?
:
: TIA
: Malc
:

Engin
Thu Oct 30 14:52:08 CST 2003

If you are using the same UserId and Password to connect
to SQL server in that connection for all connections,
your connection string is allways the same and since the
connection pooling is ON by default if you don't specify
as false in your connection string(which you don't) all
connections are hitting the same pool AND since you do
not specify the Max pool size, it probably exceeds the
default max size, which 100.

If this is the case, you may add the "ApplicationName"
option to your string along with the WebUser id and
password so that it will be different at each time, so it
will create a different pool for each login. This way,
you can follow the connections from the SQL enterprise
Manager's, Server's
Management/CurrentActivity/ProcessInfo page.


>-----Original Message-----
>We're having occasional timeouts connection to the DB
(SQL Server 2K)
>from ASP pages (Win2K + IIS 5, but no .Net). Perf Mon
on SQL Server
>indicates the User Connections sticks around 90 (+/-
5). When freqent
>timeouts appear in ASP pages, Query Analyzer, Enterprise
Manager and DTS
>packages have no problems. Thus I wonder if it's a
client side issue,
>perhaps related to connection pools under ASP.
>
>A typical connection string in our ASP pages looks like:
>"Provider=SQLOLEDB.1;Persist Security Info=True;User
>ID=xxx;Password=xxx;Initial Catalog=xxx;Data Source=xxx"
>
>How do I determine what the connection pool size is?
Presumably the
>default. How do I determine how many connections are in
use? Most of
>the answers I've seen when searching via Google seem to
pertain to
>ASP.Net, which doesn't help. We have multiple machines
running multiple
>web sites, which makes it a little harder to debug from
the server side
>(or at least, I don't know what to look for). We also
have some ISAPI
>filters and extensions written in C++ that keep their
OLE DB for
>SQLServer and OLE DB for ODBC connections open for weeks
at a time.
>They don't seem to have a problem... but could they add
to the ASP issues?
>
>TIA
>Malc
>
>.
>