Why is a SPID blocking itself in sysprocesses?
This was the question asked during an interview that was attended by one of my friend, I'm not sure how he managed to answer but came to me asking how it is possible.
For your information after you apply SQL 2000 Service Pack 4, you might observe that a SPID is blocking itself, as reported in the output of sysprocesses when you are using SP_WHO2 statement.
Further if your custom scripts/applications monitor blocking by relying on the output of sysprocesses column, they will start reporting extensive blocking on your server. So better to reduce the blocking by using smaller batches of execution. Also you might have observed by this time that SQL 2000 service pack 4 has more diagnostic information than earlier versions & SPs and the above question behaviour is explained in this KBA906344 article.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.