Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005

    Unanswered: Blocked 'sleeping' active connections


    We just installed SQL service pack 4. I am now finding that when doing a sp_who2 active, there are a lot of connections that are blocked by itself. The common factor is they all have a status of 'sleeping'. The strange thing is that even though it shows the connection is blocked, it is in fact not and will still return results. Below is a snapshot of a portion of what the sp_who2 active returns:

    SPID Status Login HostName BlkBy DBName
    53 sleeping sa TRACKER 53 dbABC
    58 sleeping sa TRACKER 58 dbCDE
    64 sleeping sa TRACKER 64 dbSTA
    66 RUNNABLE User12 PC24 . master
    70 sleeping User5 ANALYSIS 70 dbBML
    74 sleeping sa TRACKER 74 dbCDE
    76 sleeping sa TRACKER 76 dbPTS
    83 sleeping User5 ANALYSIS 83 dbANA
    86 DORMANT User11 CPTDB . NULL

    Has anyone seen this? Is it related to the installation of service pack 4? (We have installed the services pack on many other SQL servers, but have not come accross this before.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    The "self-blocking spid" is a new feature of SP4. In short, it is not actual blocking. If a spid is waiting on a latch, it shows as blocking itself. I have not seen it on this scale, though. Do you happen to know if you are suffering a lot of disk activity around the times that a lot of self-blocking spids are showing up?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts