Wednesday, March 7, 2012

Blocked sleeping active connections

Hi

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.

Tx,
TessZAThe "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?

No comments:

Post a Comment