sql server connect: My understanding of sp_reset_connection is that SQL Server
executes this SP every so often so that the settings for a
single connection within a pool are reset. Thus allowing
the same connection to be used indefinately.

However I seem to be getting errors saying that there are
no pooled connections available, so I have tried following
the connection in SQL Profiler and it seems to use the
same LoginSID.

Is there any other way of monitoring the same connection
to ensure that it is always remaining as one connection
and not creating new connections?

The above error happens when the DB server is not in use
by anyone or anything (no scheduled jobs etc), at 1 am in
the morning.

Thanks in advance.
Carl

[posted and mailed, please reply in news]

Carl (carl.farmer@dsl.pipex.com) writes:
[quoted text, click to view]

No, SQL Server does not execute this SP by itself, but it is the
client library which invokes it. Both ADO and ADO.Net employ
connection pooling. So what you are seeing is an application
problem.

[quoted text, click to view]

If you are using ADO.Net, there were two good articles on
connection pooling by Bill Vaughn in SQL Server Magazine
(www.sqlmag.com), earlier this year.

[quoted text, click to view]

You need to monitor by hostname, and see how many connection
this host creates. I believe both ADO and ADO.Net can create
new connections behind your back, if you execute a query without
having picked up all rows from the previous query.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at