IIS leaving too many SQL db connections

[Dale Mumper]

Greetings.

I have a site with several pages that connect to a SQL
2000 database with a connection string stored in a session
variable. After navigating through a number of pages with
database access, there are a plethora of database
connections left open on the server. This is an IIS 5
site on a W2K going against a SQL 2K database on the same
server. I do all of the proper things, such as:
Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionString = Session("CString")
cn.Open
' do stuff with recordset or whatever
cn.Close
Set cn = Nothing

What can I do to avoid all of these connections? In my
testing, I can get it up to 9 just as a single user in a
test environment. But I have another install where they
proclaim 26 connections.

Is this the OLE DB connection pooling figuring that I need
that many connections? It seems a wee bit excessive.

Thanks.

[Aaron Bertrand - MVP]

> Set cn = Server.CreateObject("ADODB.Connection")

> cn.ConnectionString = Session("CString")

Does this connection string change per user? If not, use a single
connection string in an application variable. If you use session level,
then if you have a user with cookies disabled, they will start a new session
on each page...

> Is this the OLE DB connection pooling figuring that I need
> that many connections? It seems a wee bit excessive.

Is this causing a problem, or is the app behaving fine and you are just
being highly analytical?

[Aaron Bertrand - MVP]

> It's not causing a performance issue at this point, so it

> is being somewhat analytical. I was just amazed that what
> I thought was sound code was leaving so much trash around.

Idle connections are not exactly "trash"... a new user picking up an idle
connection is much more efficient than the overhead of initiating a brand
new connection from the database.

Let connection pooling do its work. Worry about it when it becomes an
issue.

> And as far as setting cn.ConnectionString, I guess that
> old habits die hard. I probably will start doing it your
> way, though. Less typing makes me happy.

Okay, then see some shorter versions of connection strings. :-)
[url]http://www.aspfaq.com/2126[/url]