Thanks a lot, John! Thoes KB articles will be very helpful!

Actually, that process was running a bunch of batches which supposed to upgrade db schema. Obviously I need to modify my code (batches) so it won't sleep there while still hold all the locks. How do I trouble shoot which part might went wrong?

Also, how did you quickly find the KB articles that of your interest? I tried MSDB a few times, didn't work that well for me.

Thanks again!

Elaine

-----Original Message-----
From: John Thomas [mailto:[Email Address Removed] Thursday, August 28, 2003 3:52 PM
To: Fang, Elaine; LazyDBA.com Discussion
Subject: RE: sp_who question


Elaine,

You are right. "Sleeping" means that the process is not currently
running. It usually indicates the process waiting for a command from the
application. Now if you are in between a transaction, this process can
block other processes. The status that is showed under the status field
in the sp_lock output is the lock request status. Grant indicates that
the lock has been granted.

Check out this KB article for more info:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q224453

HTH,
John

-----Original Message-----
From: Fang, Elaine [mailto:Elaine.[Email Address Removed]
Sent: Thursday, August 28, 2003 1:16 PM
To: LazyDBA.com Discussion
Subject: sp_who question

Hi,

We are running into a situation that multiple processes are blocking
each other (not dead lock). When I use sp_who to show the processes, it
show the blocking process' status is 'sleeping'!!! So does it mean it's
not doing anything now? If that's the case, why it's still blocking
other processes?

Also, when using sp_lock, if the status column shows 'Grant', does it
mean the lock is being granted to the current process? How do I find
more detailed explaination on the fields of sp_who and sp_help. BOL
doesn't seem to be enough.

Thanks a lot!

Elaine







---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page