DECLARE @cmdVARCHAR(100),

@spidVARCHAR(4),

@SQLVARCHAR(100),

@timeDATETIME,

@loginnameVARCHAR(100)

if OBJECT_ID('TEMPDB..#TempConn2') is not null

DROP TABLE #TempConn2

create table #TempConn2

(EventType nvarchar(30), Parameters Int,EventInfo nvarchar(4000),SPID INT NULL,loginname VARCHAR(100)NULL)

--blocking

DECLARE my_cursor CURSOR FOR

SELECT [SPID],loginame

FROM sys.sysprocesses

where blocked<>0

OPEN my_cursor

FETCH NEXT FROM my_cursor

INTO @spid,@loginname

WHILE @@FETCH_STATUS = 0

BEGIN

begin

set @SQL = 'dbcc inputbuffer (' + @spid + ')'

insert into #TempConn2 (EventType ,Parameters,EventInfo)

execute(@SQL)

UPDATE #TempConn2 SET SPID=@spid,loginname=@loginname where SPID is NULL

end

FETCH NEXT FROM my_cursor

INTO @spid,@loginname

END

CLOSE my_cursor

DEALLOCATE my_cursor

select * from #TempConn2

--where eventinfo like '%text%'

GO this is what i tend run for blocking