Why is a SPID blocking itself in sysprocesses?

Published 27 July 07 01:29 AM | SQL Master 

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.

Comments

# SSQA.net - SqlServer-QA.net said on July 27, 2007 5:09 AM:

This was the question asked during an interview that was attended by one of my friend, I'm not sure

Anonymous comments are disabled

About SQL Master

**__________________________________** 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.

Search

Go

This Blog

«July 2007»
SMTWTFS
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

Syndication