connections sleeping

  • Angel DBA Mex

    Ten Centuries

    Points: 1024

    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

    Angel Miranda Nieto
    DBA Specialist, BI, MCSA SQL Server

  • Rahim+971-501387269

    Valued Member

    Points: 62

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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, MVP, M.Sc (Comp Sci)
    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
  • Gail Shaw

    SSC Guru

    Points: 1004424

    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, MVP, M.Sc (Comp Sci)
    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply