Connection pools constantly grow

I've got a little console app that basically pulls back a recordset from our SQL Server 2005, goes through each row in the dataset and may/may not insert a record into a different table in the database. We use sproc's for every transaction and I close every connection in the application. However, when the application ends, I still show connection pools open in the performance monitor. Same with websites that I know have no traffic or that have been stopped by me in IIS.

Last night I showed a total of 6000+ "Current # pooled and nonpooled connections". Should I be worried about what seems to be unending growth in the connection pools? If so, how can I look to manage this better?

Check the following article:

http://www.15seconds.com/Issue/040830.htm

Regards

|||

I actually read that article yesterday.

Perhaps I'm dealing with a connection leak. If so, I don't know how. All my transactions are either handled via:

Try SqlConnection.Open() SqlCommand.ExecuteNonQuery()Finally SqlConnection.Close()End Try

Or

Try SqlDataAdapter.Fill(DataSet,"Results")
Finally
 If SqlConnection.State = ConnectionState.OpenThen SqlConnection.Close()End If
End Try

My program uses two databases, which means that there are two connections strings. That accounts for the reason the program creates 2 connection pools when it runs, but why aren't those pools closed when the program finishes? Next time the program runs, it's increased by 2 again.

sqlsql

Connection pooling and timeouts

Does anyone have suggestions, other than using "set xact_abort on," on how
to handle rolling back a transaction after it's timed out while using
connection pooling?Richard wrote:
> Does anyone have suggestions, other than using "set xact_abort on,"
> on how to handle rolling back a transaction after it's timed out
> while using connection pooling?
Why can't the business layer executing the SQL issue a rollback if a
timeout error is returned from the call?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||That's the only way to handle it I guess. Thanks!
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23c0ci9fkFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Richard wrote:
> Why can't the business layer executing the SQL issue a rollback if a
> timeout error is returned from the call?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Connection is busy with results for another hstmt & Win2003 SP1

I wrote a web app using an ASP front end (not .NET) connecting to a SQL
Server 2000 (no SP) back end. Both the web server and the database
server are Windows 2003 boxes. The app was running fine two weeks ago
before I went on vacation. While I was gone my net admin applied
Windows 2003 SP1 to the web server. Upon my return I was informed that
the app is no longer working, getting the following error:

[Microsoft][ODBC SQL Server Driver]Connection is busy with results for
another hstmt

Since that is presumably the only thing that changed in my absence I
believe the SP somehow messed up the way the ODBC was working. I tried
moving the SQL Server database to a different Windows 2003 box which
also received the SP1 update and also includes SP3 for SQL Server, but
get the same error.

I am hitting the database like so:

set GetData = CreateObject("ADODB.Command")
GetData.ActiveConnection = SQLConn
GetData.CommandText = "<stored procedure call>"
Set DataRS= GetData.Execute (,,adCmdText)

I appear to get the error when I execute the second call. I am
learning ASP as I go, so maybe this is a trivial problem.Jegg (jsauri@.gmail.com) writes:
> I wrote a web app using an ASP front end (not .NET) connecting to a SQL
> Server 2000 (no SP) back end. Both the web server and the database
> server are Windows 2003 boxes. The app was running fine two weeks ago
> before I went on vacation. While I was gone my net admin applied
> Windows 2003 SP1 to the web server. Upon my return I was informed that
> the app is no longer working, getting the following error:
> [Microsoft][ODBC SQL Server Driver]Connection is busy with results for
> another hstmt
> Since that is presumably the only thing that changed in my absence I
> believe the SP somehow messed up the way the ODBC was working. I tried
> moving the SQL Server database to a different Windows 2003 box which
> also received the SP1 update and also includes SP3 for SQL Server, but
> get the same error.
> I am hitting the database like so:
> set GetData = CreateObject("ADODB.Command")
> GetData.ActiveConnection = SQLConn
> GetData.CommandText = "<stored procedure call>"
> Set DataRS= GetData.Execute (,,adCmdText)
> I appear to get the error when I execute the second call. I am
> learning ASP as I go, so maybe this is a trivial problem.

The gist of the error message is that you have a command that generated
one or more results, that you have not picked up, and you cannot submit
the next stored procedure for execution.

If that procedure generates result sets, you probably want that data.
Then again, it could be a stray debug result set that should not be
there.

Here are some general rules:
o Unless you want explicit row counts back from INSERT/UPDAET/DELETE
operations, submit a SET NOCOUNT ON when you connect. These rowcounts
are actually kind of result sets, and these need to be consumed.
SET NOCOUNT ON eliminates those.
o If you call a stored procedure that is not supposed to return data,
specify the option adExecuteNoRecords.
o When you run a procedure that can return data, be sure to get all
record sets, by looping over .NextRecordset.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info. I did have nocount set to on. However, I did not
know about the adExecuteNoRecords option. That actually will come in
very handy.

On a whim I did manage to elimate the error by adding "DataRS = empty"
after the first stored procedure call. I did not have to do this with
any of the others presumably because I loop through the result set
until EOF (there was known to be only one possible record in the first
result set so I did not do this).

Thanks for your speedy reply!

Connection error while already running code

I am using Access 2002 as a front end and SQL Server 2000 as a back end.
I am using an ODBC connection and TCP/IP
After I have connected, I use ADO to run a process that gets ten different
sum queries from various SQL Server tables and writes the results to local
Access table. This loops thru 300 to 800 records.
The second time thru this process, at any of the queries, I will almost
always get this error:
-2147467259, Sql Server does not exist or access denied. Now I am already
connected and I have run this same query many times.
If I debug and force the same line, it runs.
Why is it hanging occasionally, and more importantly, what can I do to fix
this?
TIA,
MikeCould be that the problem is timing related. What happens if you insert a
timer event to wait a few seconds prior to establishing a new connection?
What is the connection timeout setting? How many concurrent connections
are on the server when this happens?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
I am not establishing a new connection, I am using one that is already open.
The connection timeout is the default, 30 seconds.
The concurrent connection fluctuates between 159 and 175.
Hope this helps.
Mike
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:dzu1LfLVEHA.2764@.cpmsftngxa10.phx.gbl...
> Could be that the problem is timing related. What happens if you insert a
> timer event to wait a few seconds prior to establishing a new connection?
> What is the connection timeout setting? How many concurrent connections
> are on the server when this happens?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

Connection error while already running code

I am using Access 2002 as a front end and SQL Server 2000 as a back end.
I am using an ODBC connection and TCP/IP
After I have connected, I use ADO to run a process that gets ten different
sum queries from various SQL Server tables and writes the results to local
Access table. This loops thru 300 to 800 records.
The second time thru this process, at any of the queries, I will almost
always get this error:
-2147467259, Sql Server does not exist or access denied. Now I am already
connected and I have run this same query many times.
If I debug and force the same line, it runs.
Why is it hanging occasionally, and more importantly, what can I do to fix
this?
TIA,
Mike
Could be that the problem is timing related. What happens if you insert a
timer event to wait a few seconds prior to establishing a new connection?
What is the connection timeout setting? How many concurrent connections
are on the server when this happens?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Kevin,
I am not establishing a new connection, I am using one that is already open.
The connection timeout is the default, 30 seconds.
The concurrent connection fluctuates between 159 and 175.
Hope this helps.
Mike
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:dzu1LfLVEHA.2764@.cpmsftngxa10.phx.gbl...
> Could be that the problem is timing related. What happens if you insert a
> timer event to wait a few seconds prior to establishing a new connection?
> What is the connection timeout setting? How many concurrent connections
> are on the server when this happens?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

Connection error after already connected

I am using Access 2002 as a front end and SQL Server 2000 as a back end.
After I have connected, I use ADO to run a process that gets ten different
sum queries from various SQL Server tables and writes the results to local
Access table. This loops thru 300 to 800 records.
The second time thru this process, at any of the queries, I will almost
always get this error:
-2147467259, Sql Server does not exist or access denied. Now I am already
connected and I have run this same query many times.
If I debug and force the same line, it runs.
Why is it hanging occasionally, and more importantly, what can I do to fix
this?
TIA,
Mike
Are you using named pipes or tcp/ip ?
Vikram
"Michael Beck" <mikeb@.Nospamamuletc.com> wrote in message
news:OxuPzTwTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> I am using Access 2002 as a front end and SQL Server 2000 as a back end.
> After I have connected, I use ADO to run a process that gets ten different
> sum queries from various SQL Server tables and writes the results to local
> Access table. This loops thru 300 to 800 records.
> The second time thru this process, at any of the queries, I will almost
> always get this error:
> -2147467259, Sql Server does not exist or access denied. Now I am already
> connected and I have run this same query many times.
> If I debug and force the same line, it runs.
> Why is it hanging occasionally, and more importantly, what can I do to fix
> this?
> TIA,
> Mike
>
|||TCP/IP
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:efh1gyyTEHA.3180@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Are you using named pipes or tcp/ip ?
> Vikram
> "Michael Beck" <mikeb@.Nospamamuletc.com> wrote in message
> news:OxuPzTwTEHA.3336@.TK2MSFTNGP10.phx.gbl...
different[vbcol=seagreen]
local[vbcol=seagreen]
already[vbcol=seagreen]
fix
>

Connection error after already connected

I am using Access 2002 as a front end and SQL Server 2000 as a back end.
After I have connected, I use ADO to run a process that gets ten different
sum queries from various SQL Server tables and writes the results to local
Access table. This loops thru 300 to 800 records.
The second time thru this process, at any of the queries, I will almost
always get this error:
-2147467259, Sql Server does not exist or access denied. Now I am already
connected and I have run this same query many times.
If I debug and force the same line, it runs.
Why is it hanging occasionally, and more importantly, what can I do to fix
this?
TIA,
MikeAre you using named pipes or tcp/ip ?
Vikram
"Michael Beck" <mikeb@.Nospamamuletc.com> wrote in message
news:OxuPzTwTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> I am using Access 2002 as a front end and SQL Server 2000 as a back end.
> After I have connected, I use ADO to run a process that gets ten different
> sum queries from various SQL Server tables and writes the results to local
> Access table. This loops thru 300 to 800 records.
> The second time thru this process, at any of the queries, I will almost
> always get this error:
> -2147467259, Sql Server does not exist or access denied. Now I am already
> connected and I have run this same query many times.
> If I debug and force the same line, it runs.
> Why is it hanging occasionally, and more importantly, what can I do to fix
> this?
> TIA,
> Mike
>|||TCP/IP
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:efh1gyyTEHA.3180@.TK2MSFTNGP11.phx.gbl...
> Are you using named pipes or tcp/ip ?
> Vikram
> "Michael Beck" <mikeb@.Nospamamuletc.com> wrote in message
> news:OxuPzTwTEHA.3336@.TK2MSFTNGP10.phx.gbl...
different[vbcol=seagreen]
local[vbcol=seagreen]
already[vbcol=seagreen]
fix[vbcol=seagreen]
>

connection based temp tables

Hello

I am developing a web based webshop with a ms sql back end, but I
cannot figure out how to do connection based temp tables, so that each
user gets their own temp table to hold the purchased items.

any ideas or hints would bery much appreciated

rgds

Matt<matt@.fruitsalad.org> wrote in message
news:1126360634.762880.315890@.g43g2000cwa.googlegr oups.com...
> Hello
> I am developing a web based webshop with a ms sql back end, but I
> cannot figure out how to do connection based temp tables, so that each
> user gets their own temp table to hold the purchased items.
> any ideas or hints would bery much appreciated
> rgds
> Matt

A better idea is probably to use a single table, with a customer ID or
session ID as part of the key. That way you can store items for some time
and you don't lose any information if the connection from your middle tier
is broken for some reason. You also have an easy way to view aggregate
information for all customers/sessions, and so on.

Since normal temp tables exist per connection and you probably won't have
one new client connection per customer from the middle tier to the database,
it's doubtful if temp tables would be a good solution for you.

Simon|||Dear Matt,
You'll have a tricky time with this approach to shopping carts I think.
Whilst you can create a temp table that persists for only as long as the
connection to database exists - you'd have to keep the database connected to
the webserver permanently and once for every user of the website. This would
be packed full of problems - not least of which would be that you'd run out
of connections pretty quickly.

You've got two main options.

1) Keep the shopping cart on the webserver not in the database - this has a
lot of advantages - 1) If you're choice of scripting language manages
sessions for you (like asp.net) then you're users shopping carts will
dissapear without you having to manage the process of cleaning them up. 2)
It'd be much faster - you wouldn't have to keep hitting the database to add
something into the cart, or to pull up the cart items. 3) You wouldn't hve
to clean up the database for abandoned order (your rate might be as high as
70-80% of orders that get abandoned).

2) Keep the cart in a
"pending orders" table - and keep track of the time that each item was
added, and group the items together either by a cart id or by a customer
number - then you could automate clean up.

Nick
<matt@.fruitsalad.org> wrote in message
news:1126360634.762880.315890@.g43g2000cwa.googlegr oups.com...
> Hello
> I am developing a web based webshop with a ms sql back end, but I
> cannot figure out how to do connection based temp tables, so that each
> user gets their own temp table to hold the purchased items.
> any ideas or hints would bery much appreciated
> rgds
> Matt