connections sleeping

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Testing Center » Question of the Day (QOD) » connections sleeping

connections sleeping

Rate Topic

Display Mode

Topic Options

Author

Message

Angel DBA Mex

Posted Tuesday, June 05, 2007 11:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:35 PM
Points: 33, Visits: 328

i have a doubt, when i execute sp_who the display send me de current conections, but almost case there are a connections with the following information

53 0 sleeping NT AUTHORITY\SYSTEM SIMIMX-SIF2 0 msdb AWAITING COMMAND
54 0 sleeping NT AUTHORITY\SYSTEM SIMIMX-SIF2 0 master AWAITING COMMAND
55 0 sleeping NT AUTHORITY\SYSTEM SIMIMX-SIF2 0 master AWAITING COMMAND
56 0 sleeping NT AUTHORITY\SYSTEM SIMIMX-SIF2 0 master AWAITING COMMAND
57 0 sleeping NT AUTHORITY\SYSTEM SIMIMX-SIF2 0 master AWAITING COMMAND

as you would see, is to me system information that it`s sleeping, and i have more, near to 100 processes that appear daily, could you help me how clearing this proccesses, maybe with a period of time, thank a lot

Post #371400

Rahim+971-501387269

Posted Monday, January 28, 2013 1:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, March 03, 2013 5:31 AM
Points: 4, Visits: 48

U can use
DBCC FREEPOCCACHE Command to clear them regularly or
create a job that will do late in the night

for more info refer http://msdn2.microsoft.com/en-us/library/ms174283.aspx

Post #1412246

GilaMonster

Posted Monday, January 28, 2013 2:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 37,651, Visits: 29,904

Rahim-441309 (1/28/2013)

U can use
DBCC FREEPOCCACHE Command to clear them regularly or
create a job that will do late in the night

Errr... no. FreeProcCache does nothing to connections, it, as the command's name implies, frees the procedure cache, which is a bad thing on a production server.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1412253

GilaMonster

Posted Monday, January 28, 2013 2:05 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 37,651, Visits: 29,904

Angel DBA Mex (6/5/2007)

could you help me how clearing this proccesses, maybe with a period of time, thank a lot

Why?

A sleeping connection is not doing anything, it's waiting for some input from the application. What's the problem that lots of sleeping connections is causing, why do you want to remove them?

Do you know what impact it'll have on the application if those connections are killed? Will the app handle the loss of connection or will it throw errors the next time the user tries anything?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1412256

« Prev Topic | Next Topic »

Permissions