SQL Server has too many connections

[STom]

We have the following settings in our web.config file to store session state
on the SQL Server.

<sessionState
mode="SQLServer"
sqlConnectionString="data source=h7sql;Integrated Security=SSPI"
cookieless="false"
timeout="200"
/>

We have found that we have a lot (80+ for 1 user) of connections to the
database left open for multiple days (by looking at our SQL Server monitor).

We had assumed that once the user logged off, the connection would be taken
away and then when a new user logged in, they would get their own
connections.

So I was wondering what connection activity people are seeing on their
servers?

Thanks.

STom

[Steve C. Orr [MVP, MCSD]]

How are you instantiating/storing your database connections?
You should open a database connection just before you need it on a page, and
then close the database connection as soon as possible when you're done
using it. Connection pooling generally makes this very efficient.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com


"STom" <st***********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

We have the following settings in our web.config file to store session state on the SQL Server.

<sessionState
mode="SQLServer"
sqlConnectionString="data source=h7sql;Integrated Security=SSPI" cookieless="false"
timeout="200"
/>

We have found that we have a lot (80+ for 1 user) of connections to the
database left open for multiple days (by looking at our SQL Server monitor).
We had assumed that once the user logged off, the connection would be taken away and then when a new user logged in, they would get their own
connections.

So I was wondering what connection activity people are seeing on their
servers?

Thanks.

STom

[STom]

We are not opening or closing anything. It is asp.net that is doing the
opening and closing for the session state to be stored on SQL server. Am I
missing something here?

STom
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:eP**************@TK2MSFTNGP12.phx.gbl...

How are you instantiating/storing your database connections?
You should open a database connection just before you need it on a page, and then close the database connection as soon as possible when you're done
using it. Connection pooling generally makes this very efficient.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com


"STom" <st***********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

We have the following settings in our web.config file to store session

state

on the SQL Server.

<sessionState
mode="SQLServer"
sqlConnectionString="data source=h7sql;Integrated

Security=SSPI"

cookieless="false"
timeout="200"
/>

We have found that we have a lot (80+ for 1 user) of connections to the
database left open for multiple days (by looking at our SQL Server

monitor).

We had assumed that once the user logged off, the connection would be

taken

away and then when a new user logged in, they would get their own
connections.

So I was wondering what connection activity people are seeing on their
servers?

Thanks.

STom

[Scott Allen]

STom:

Asp.net could just be opening up a pool of connections to use. It
defintely does not assign a connection per user to manage session
state. 80+ seems a little high though. Does your app also open
connections to SQL Server?

--
Scott
http://www.OdeToCode.com

On Mon, 12 Jan 2004 13:14:47 -0500, "STom" <st***********@hotmail.com>
wrote:

We are not opening or closing anything. It is asp.net that is doing the
opening and closing for the session state to be stored on SQL server. Am I
missing something here?

STom
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:eP**************@TK2MSFTNGP12.phx.gbl...

How are you instantiating/storing your database connections?
You should open a database connection just before you need it on a page,

and

then close the database connection as soon as possible when you're done
using it. Connection pooling generally makes this very efficient.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com


"STom" <st***********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

> We have the following settings in our web.config file to store session

state

> on the SQL Server.
>
> <sessionState
> mode="SQLServer"
> sqlConnectionString="data source=h7sql;Integrated

Security=SSPI"

> cookieless="false"
> timeout="200"
> />
>
> We have found that we have a lot (80+ for 1 user) of connections to the
> database left open for multiple days (by looking at our SQL Server

monitor).

>
> We had assumed that once the user logged off, the connection would be

taken

> away and then when a new user logged in, they would get their own
> connections.
>
> So I was wondering what connection activity people are seeing on their
> servers?
>
> Thanks.
>
> STom
>
>

[STom]

No, the app does not open connections to the database, we have business
objects on the app server that do that but we can tell that it appears to be
the logged in user account that is connected plus it is the connections to
the asp state database that have a large number of connections.

STom
"Scott Allen" <bitmask@[nospam].fred.net> wrote in message
news:fj********************************@4ax.com...

STom:

Asp.net could just be opening up a pool of connections to use. It
defintely does not assign a connection per user to manage session
state. 80+ seems a little high though. Does your app also open
connections to SQL Server?

--
Scott
http://www.OdeToCode.com

On Mon, 12 Jan 2004 13:14:47 -0500, "STom" <st***********@hotmail.com>
wrote:

We are not opening or closing anything. It is asp.net that is doing the
opening and closing for the session state to be stored on SQL server. Am Imissing something here?

STom
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:eP**************@TK2MSFTNGP12.phx.gbl...

How are you instantiating/storing your database connections?
You should open a database connection just before you need it on a page,

and

then close the database connection as soon as possible when you're done
using it. Connection pooling generally makes this very efficient.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com


"STom" <st***********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
> We have the following settings in our web.config file to store

session state
> on the SQL Server.
>
> <sessionState
> mode="SQLServer"
> sqlConnectionString="data source=h7sql;Integrated
Security=SSPI"
> cookieless="false"
> timeout="200"
> />
>
> We have found that we have a lot (80+ for 1 user) of connections to the > database left open for multiple days (by looking at our SQL Server
monitor).
>
> We had assumed that once the user logged off, the connection would be
taken
> away and then when a new user logged in, they would get their own
> connections.
>
> So I was wondering what connection activity people are seeing on their > servers?
>
> Thanks.
>
> STom
>
>