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 Management

Hi:

I have a Point-of-sale application that uses SQL Server2000 for the backend.
Basically, the users perform various functions boiling down to login (check
password from a table) and data entry (insert a food entry). Previously, I
would open a new ADO 2.7 connection to the database each time one of these
types of database accessing functions needed to be performed - but I noticed
that sometimes the DB would freeze the application for 20 seconds or so - or
even cause a timeout error.

To fix this, I open a DB connection when the application first starts,
keeping it open for the life of the application - each time a function needs
to access the DB, it just uses the applications (global) connection that is
constantly open and connected.

This seems to have fixed the problem, however, I am curious, is this an OK
way to handle the connections - keeping in mind that there are four separate
stations - each running the application - at the same time. Therefore, I
have 4 constantly open connections at the same time.

Thanks and regards,

Ryan Kennedy"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:lYnBb.4549$UM4.1037@.nwrdny01.gnilink.net...
> Hi:
> I have a Point-of-sale application that uses SQL Server2000 for the
backend.
> Basically, the users perform various functions boiling down to login
(check
> password from a table) and data entry (insert a food entry). Previously,
I
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I
noticed
> that sometimes the DB would freeze the application for 20 seconds or so -
or
> even cause a timeout error.
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function
needs
> to access the DB, it just uses the applications (global) connection that
is
> constantly open and connected.
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four
separate
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.
>
> Thanks and regards,
> Ryan Kennedy

I don't know much about ADO, but it sounds like you're describing a form of
connection pooling, which is certainly a very common way to manage
connections from multiple clients.

Simon|||"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:lYnBb.4549$UM4.1037@.nwrdny01.gnilink.net...
> Hi:
> I have a Point-of-sale application that uses SQL Server2000 for the
backend.
> Basically, the users perform various functions boiling down to login
(check
> password from a table) and data entry (insert a food entry). Previously,
I
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I
noticed
> that sometimes the DB would freeze the application for 20 seconds or so -
or
> even cause a timeout error.
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function
needs
> to access the DB, it just uses the applications (global) connection that
is
> constantly open and connected.
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four
separate
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.

Connection pooling, the sharing of a single connection among components of
an application, is very common and a good design principle. It is
particularly great for web based/ASP applications, in order to conserve
resource. Persistant connections, keeping a connection open even when not in
use, is something I shy away from in my client server and my web based app
designs. I prefer to create a connection, pool it, and then open and close
it as needed.

It sounds like you are looking for a solution to a symptom, and not your
problem. If I were you, I would investigate the reason your app is timing
out and solve that.

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

Connection gets lost

Hi,
I have a DSN set-up to access a SQL Server database from another
machine and program.
It is basically an import manager (third party product from an
external company), when I import it loads in a few thousand records
and then after about 2 minutes I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
It then reconnects within a few minutes but misses loading the record
where the error happens.
Can someone help me locate the problem and how to fix it.
The external company has no problems with loading so i can only
imagine it is our SQL Server set-up
Thanks
ArchanaHi
"archanapatelwhite@.googlemail.com" wrote:
> Hi,
> I have a DSN set-up to access a SQL Server database from another
> machine and program.
> It is basically an import manager (third party product from an
> external company), when I import it loads in a few thousand records
> and then after about 2 minutes I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> exist or access denied.
> It then reconnects within a few minutes but misses loading the record
> where the error happens.
> Can someone help me locate the problem and how to fix it.
> The external company has no problems with loading so i can only
> imagine it is our SQL Server set-up
> Thanks
> Archana
>
I would initially suspect something like your Anti-virus software,
anti-intusion software or firewall is breaking the connection e.g
http://support.microsoft.com/kb/170338/en-us
You may want to also check if you are on a recent SQL Server service pack
see
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John

Connection gets lost

Hi,
I have a DSN set-up to access a SQL Server database from another
machine and program.
It is basically an import manager (third party product from an
external company), when I import it loads in a few thousand records
and then after about 2 minutes I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
It then reconnects within a few minutes but misses loading the record
where the error happens.
Can someone help me locate the problem and how to fix it.
The external company has no problems with loading so i can only
imagine it is our SQL Server set-up
Thanks
Archana
Hi
"archanapatelwhite@.googlemail.com" wrote:

> Hi,
> I have a DSN set-up to access a SQL Server database from another
> machine and program.
> It is basically an import manager (third party product from an
> external company), when I import it loads in a few thousand records
> and then after about 2 minutes I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> exist or access denied.
> It then reconnects within a few minutes but misses loading the record
> where the error happens.
> Can someone help me locate the problem and how to fix it.
> The external company has no problems with loading so i can only
> imagine it is our SQL Server set-up
> Thanks
> Archana
>
I would initially suspect something like your Anti-virus software,
anti-intusion software or firewall is breaking the connection e.g
http://support.microsoft.com/kb/170338/en-us
You may want to also check if you are on a recent SQL Server service pack
see
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John

Connection gets lost

Hi,
I have a DSN set-up to access a SQL Server database from another
machine and program.
It is basically an import manager (third party product from an
external company), when I import it loads in a few thousand records
and then after about 2 minutes I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server do
es not
exist or access denied.
It then reconnects within a few minutes but misses loading the record
where the error happens.
Can someone help me locate the problem and how to fix it.
The external company has no problems with loading so i can only
imagine it is our SQL Server set-up
Thanks
ArchanaHi
"archanapatelwhite@.googlemail.com" wrote:

> Hi,
> I have a DSN set-up to access a SQL Server database from another
> machine and program.
> It is basically an import manager (third party product from an
> external company), when I import it loads in a few thousand records
> and then after about 2 minutes I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server
does not
> exist or access denied.
> It then reconnects within a few minutes but misses loading the record
> where the error happens.
> Can someone help me locate the problem and how to fix it.
> The external company has no problems with loading so i can only
> imagine it is our SQL Server set-up
> Thanks
> Archana
>
I would initially suspect something like your Anti-virus software,
anti-intusion software or firewall is breaking the connection e.g
http://support.microsoft.com/kb/170338/en-us
You may want to also check if you are on a recent SQL Server service pack
see
http://sqlserver2000.databases.aspf...-m-running.html
John