Query to find spids at head of a blocking chain, their input buffers, and the type of blocking locks they hold


declare @blocker_spid smallint
declare @i_buff_string char(30)
set nocount on

/* Get all blocked spids */

select spid, blocked, hostname=substring (hostname, 1, 10),
progname=substring(program_name, 1, 10), cmd=substring(cmd, 1, 10),
status, physical_io, waittype
into #blk from master..sysprocesses (nolock) where blocked != 0

/* delete all blocking spids except the 1st in each blocking chain */

delete from #blk
where blocked in (select spid from #blk)

/* get each spid from sysprocesses which is referenced in */
/* the "blocked" column of #blk. This should be the head */
/* of each blocking chain */

select "Blocking spid" = spid, loginame=substring(suser_name(sid),1,10),
hostname=substring (hostname, 1, 10), progname=substring(program_name, 1,10),
cmd=substring(cmd, 1, 10), status, physical_io, waittype
from master..sysprocesses (nolock)
where spid in
(select blocked from #blk)

/* For each spid at the head of a blocking chain */
/* print its input buffer to show what query it's running */

declare blk_cursor CURSOR FOR SELECT blocked from #blk
open blk_cursor
fetch next from blk_cursor into @blocker_spid
while (@@fetch_status <> -1)
begin
select @i_buff_string = ('dbcc inputbuffer (' +
convert(char(6),@blocker_spid) +')')
select 'Below is input buffer for this blocking spid: ', @blocker_spid
select ' '
exec (@i_buff_string)
fetch next from blk_cursor into @blocker_spid
end
deallocate blk_cursor


drop table #blk