471,066 Members | 1,195 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

SQL Server has too many connections

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
Nov 18 '05 #1
4 5220
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

Nov 18 '05 #2
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


Nov 18 '05 #3
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
>
>



Nov 18 '05 #4
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
>
>

Nov 18 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Fortepianissimo | last post: by
2 posts views Thread by Patrick | last post: by
reply views Thread by Macca | last post: by
4 posts views Thread by Engineerik | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.