set nocount on;

declare @bufferSPID table (EventType nvarchar(30), Parameter smallint, EventInfo nvarchar(4000));

declare @bufferBLOCKED table (EventType nvarchar(30), Parameter smallint, EventInfo nvarchar(4000));

declare @ret table (spid int, spidEventInfo nvarchar(4000), blocked int, blockedEventInfo nvarchar(4000), hostname varchar(100), loginame varchar(100));

DECLARE @ospid int,

@oblocked int,

@ohostname varchar(100),

@ologinname varchar(100),

@spidEventInfo nvarchar(4000),

@blockedEventInfo nvarchar(4000)

DECLARE indexCursor CURSOR FOR

select spid, blocked, hostname, loginame

from sys.sysprocesses

where [status] = 'suspended' -- tasks that are waiting for I/O to complete

OPEN indexCursor

FETCH NEXT FROM indexCursor INTO @ospid, @oblocked, @ohostname, @ologinname

WHILE (@@FETCH_STATUS = 0)

BEGIN

delete from @bufferSPID

delete from @bufferBLOCKED

if(@ospid <> 0) insert into @bufferSPID exec ('DBCC INPUTBUFFER(' + @ospid + ')');

if(@oblocked <> 0) insert into @bufferBLOCKED exec ('DBCC INPUTBUFFER(' + @oblocked + ')');

select @spidEventInfo= EventInfo from @bufferSPID

select @blockedEventInfo = EventInfo from @bufferBLOCKED

insert into @ret (spid, spidEventInfo, blocked, blockedEventInfo, hostname, loginame)

select @ospid, @spidEventInfo, @oblocked, @blockedEventInfo, @ohostname, @ologinname

FETCH NEXT FROM indexCursor into @ospid, @oblocked, @ohostname, @ologinname

END

CLOSE indexCursor

DEALLOCATE indexCursor

select * from @ret