whos blocking

-- A quick little standalone script that tells you what process is blocking and what processes the blocking processing actually blocking.

-- When running this script in QA, change your output to "Results in Text" ( CTRL-T ).  Utilizes the blocking info in sp_who2 combined with dbcc inputbuffer and a little cursor to wrap it all up.  Formatting needs a little work, but the info is all there.

set nocount on

declare @spid varchar(10)

declare @blkby varchar(10)

declare @stmt varchar(100)

if not exists ( select top 1 name from tempdb..sysobjects where name like '#temp%' )

begin

   create table #temp ( spid integer, status varchar(100), login varchar(50), hostname varchar(25), blkby varchar(10), 

                        dbname varchar(25), command varchar(100), cputime integer, diskio integer, lastbatch varchar(25), 

                        programname varchar(255), spid2 integer )

end

else

begin

   truncate table #temp

end

insert into #temp

exec sp_who2

declare curs cursor for

select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%'

open curs

fetch next from curs into @spid, @blkby

while @@fetch_status = 0

begin

   set @stmt = 'dbcc inputbuffer(' + @blkby + ')'

   raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait

   exec (@stmt)

   raiserror('SPID that is Blocked:%s',0,1,@spid) with nowait

   set @stmt = 'dbcc inputbuffer(' + convert(varchar(10), @spid) + ')'

   exec (@stmt)

   fetch next from curs into @spid, @blkby

end

close curs 

deallocate curs