Tsql Script to get Blocking Report

Tsql Script to get Blocking Report

SET NOCOUNT ON

DECLARE @Handle varbinary(64);

DECLARE @SPID INT;

declare @SpidStatus varchar(100),@BlockerSpid int, @BlockedBy int,@DBname sysname,@CTEXT VARCHAR(8000)

DECLARE [Blocked_Spids] CURSOR FOR

select case when blocked != 0 then 'Not Lead Blocked'

when blocked = 0 then 'Lead Blocker'

Else 'Unknown'

End [Block],

spid,blocked, db_name(dbid) dbname from master..sysprocesses

where (blocked = 0 and spid in (select blocked from master..sysprocesses)) or blocked != 0 order by blocked asc, waittime desc

FOR READ ONLY



OPEN [Blocked_Spids]

WHILE 1 = 1

BEGIN



FETCH NEXT FROM [Blocked_Spids] INTO @SpidStatus, @BlockerSpid,@BlockedBy,@DBname

IF @@fetch_status <> 0 BREAK

SELECT RTRIM(LTRIM(STR(@BlockerSpid))) + ' '+ @SpidStatus +' In Database '+@DBname + ' Is running following T-SQL and is Blcoked by '+ ltrim(STR(@BlockedBy))

SELECT @Handle = sql_handle

FROM master..sysprocesses

WHERE spid = @BlockerSpid

SELECT @CTEXT=text FROM ::fn_get_sql(@Handle);

IF @CTEXT IS NULL OR LEN(LTRIM(RTRIM(@CTEXT)))=0

BEGIN

SELECT 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockerSpid)) +')'

DBCC INPUTBUFFER(@BlockerSpid)

END

ELSE

SELECT @CTEXT

SELECT 'LOCK STATUS ON SPID '+ STR(@BlockerSpid)

EXECUTE sp_lock @BlockerSpid

END



deallocate [Blocked_Spids]