How to find sessions that are blocked?

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s