To find sessions that are blocking a session and blocked session, one can run the following SQL which works on SQLServer 2000 and SQL Server 2005.
select spid, blocked, dbid, open_tran, status, hostname, loginame, waittime, waitresource, login_time
from master..sysprocesses
where spid in (select blocked from
master..sysprocesses) or blocked != 0
Sample Ouptut: In this example it shows spid 53 is waiting for spid 52, the blocked value for spid 53 is 52.
spid blocked dbid open_tran status hostname loginame waittime waitresource login_time
—— ——- —— ——— —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ——————– —————————————————————————————————————————————————————————————————————————————————————- ———————–
52 0 1 1 runnable HOST amin 0 2010-03-20 10:38:38.903
53 52 1 0 suspended HOST amin 2914937 RID: 1:1:238:1 2010-03-20 11:40:19.530
alexlima.com 