Why is a SPID blocking itself in sysprocesses? How is it possible?
After you apply SQL 2000 Service Pack 4, you might observe that a SPID is blocking itself, as reported in the output of sysprocesses.
If your custom scripts/applications monitor blocking by relying on the output of sysprocesses column, they will start reporting extensive blocking on your server.
However, there is more to it than meets the eye.
Reason
SQL 2000 SP4 reports more diagnostic information than earlier versions. In addition to lock waits, SQL 2000 SP4 now reports latch waits as well.
It is this new monitoring which could lead to the situation described above – a SPID blocking itself.
In majority of the cases, you can ignore this condition if either the value in the waittime column is low OR the waittype of the SPID is a latch waittype.
For more information on this, please refer to the KB article 906344.
POSTED BY : Sanchan Saxena
