Description:
Example to show the blocked processes within a instance (who is blocking who),
Check the example below:
------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
SELECT *
FROM master..sysprocesses
GO
DECLARE @spid int
DECLARE @rowcount int
DECLARE @dbccspid nvarchar(4000)
CREATE TABLE #dbccbuffer(EventType nvarchar(30), [Parameters] int, EventInfo nvarchar(4000))
CREATE TABLE #handle ([SPID] int, [SQLHandle] text, [DBCCinput] nvarchar(4000))
SELECT blocked
INTO #blocked
FROM master..sysprocesses
WHERE blocked <> 0
SET @rowcount = (SELECT count(*) FROM #blocked)
WHILE @rowcount <> 0
BEGIN
SELECT @spid = blocked
FROM #blocked
ORDER BY 1 asc
BEGIN
INSERT INTO #handle (spid, [SQLHandle])
SELECT @spid, [text] FROM ::fn_get_sql((
SELECT [sql_handle]
FROM master..sysprocesses
WHERE spid = @spid
))
SELECT @dbccspid = 'dbcc inputbuffer('+cast(@spid AS nvarchar(10))+')'
INSERT #dbccbuffer
EXEC sp_executesql @dbccspid
UPDATE #handle
SET DBCCinput = (SELECT eventinfo FROM #dbccbuffer)
WHERE spid = @spid
DELETE #dbccbuffer
END
DELETE FROM #blocked WHERE blocked = @spid
SET @rowcount = @rowcount -1
END
SELECT *
FROM #handle
GO
DROP TABLE #handle
DROP TABLE #blocked
DROP TABLE #dbccbuffer
GO