Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Sleeping process... Expand / Collapse
Author
Message
Posted Friday, May 8, 2009 1:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 29, 2010 8:01 PM
Points: 7, Visits: 19
Hi Brian,

I am facing a similar problem in my project - I don't see the issue in development, but when we move to production the number of sleeping connections grow rapidly and sql server starts refusing new connections (the software is pretty useless at this stage).

You mention that after closing, sql server holds the connection for a period of time (1 min or so). Is there any documentation that gives this information and also any idea whether this number is tunable/Changeable ?

thanks

Anand
Post #712674
Posted Friday, May 8, 2009 3:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 5, 2013 7:04 AM
Points: 1,197, Visits: 294
Anand S (5/8/2009)
Hi Brian,

I am facing a similar problem in my project - I don't see the issue in development, but when we move to production the number of sleeping connections grow rapidly and sql server starts refusing new connections (the software is pretty useless at this stage).

You mention that after closing, sql server holds the connection for a period of time (1 min or so). Is there any documentation that gives this information and also any idea whether this number is tunable/Changeable ?

thanks

Anand

Yes, there is documentation. You need to check the specific one for you development language/environment. The connection pooling is on your application side and not on your DB side... In most languages you can turn connection pooling off...

(Maybe can be worth checking into your connection strings, that they are the same over the whole application. Constantly changing configuration strings makes connection pooling almost useless).

Not using a DAL approach might also negate the use of connection pooling...



Post #712746
Posted Friday, May 8, 2009 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 29, 2010 8:01 PM
Points: 7, Visits: 19
Hi,

I am using Asp.net webservice to invoke the connections. In this case does .Net provide connection pooling. I thought the db creates and manages the connection pool.

We are creating the connection string using default values, so connection pooling is true. We use one single connection string , so the connections are for the same string.

Not using a DAL approach might also negate the use of connection pooling...
>> We are using a data access layer approach by having the connection,sql in a separate class

The transactions are committed and disposed. Connections are closed on success (Also rollbacks, connection close on exceptions).

The connections continue in "sleeping" status even after the close. What would cause that? and how is the time configurable - i.e. the time that sqlserver maintains a connection in active status after it is closed by .net ?
Post #712872
Posted Saturday, October 6, 2012 4:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:09 PM
Points: 549, Visits: 1,181
It may happen even if the user who executed the query in sql server may not closed the query window from long time once if the query window closed then you can't see the spid in the activity monitor

Thanks & Regards
NAGA.ROHITKUMAR
Post #1369410
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse