Here's an alternate version that doesn't use sp_who yet provides a little more information. It also provides the option to kill the blocking processes themself.
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ListBlocking]')
AND type in (N'P', N'PC'))
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ListBlocking] AS'
GO
ALTER procedure ListBlocking
(
@KillOrphanedProcesses bit = 0,
@KillBlockingProcesses bit = 0
)
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('tempdb..#BlockingProcess') IS NOT NULL drop table #BlockingProcess
CREATE TABLE #BlockingProcess
(
BlockingProcessID int IDENTITY(1,1),
ProcessID varchar(20),
EventType varchar(100),
Parameters varchar(100),
EventInfo varchar(500),
CPUTime int,
DiskIO int,
TransactionCount int,
Command varchar(max),
ObjectName varchar(100),
TransactionIsolation varchar(50)
)
DECLARE @BlockingSPID int,
@CPUTime int,
@DiskIO int,
@TransactionCount int,
@Command varchar(max),
@ObjectName varchar(100),
@TransactionIsolation varchar(50)
SET NOCOUNT ON
SELECT *
INTO #ProcessRaw
FROM
(
SELECT SPID = BlockingRequest.session_id,
Status = Session.status,
BlockedBy = BlockingRequest.blocking_session_id,
Command = SUBSTRING(SqlText.text, BlockingRequest.statement_start_offset/2,
(CASE WHEN BlockingRequest.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), SqlText.text)) * 2
ELSE BlockingRequest.statement_end_offset
END - BlockingRequest.statement_start_offset)/2),
ObjectName = OBJECT_SCHEMA_NAME(SqlText.objectid,dbid) + '.'
+ OBJECT_NAME(SqlText.objectid, SqlText.dbid),
StartTime = BlockingRequest.start_time,
ElapsedMS = BlockingRequest.total_elapsed_time,
CPUTime = BlockingRequest.cpu_time,
IOReads = BlockingRequest.logical_reads + BlockingRequest.reads,
IOWrites = BlockingRequest.writes,
LastWaitType = BlockingRequest.last_wait_type,
Protocol = Connection.net_transport,
TransactionIsolation =
CASE Session.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END,
ConnectionWrites = Connection.num_writes,
ConnectionReads = Connection.num_reads,
ClientAddress = Connection.client_net_address,
Authentication = Connection.auth_scheme,
[Login] = Session.login_name,
Host = Session.host_name,
DBName = DB_Name(BlockingRequest.database_id),
CommandType = BlockingRequest.command
FROM sys.dm_exec_requests Request
LEFT JOIN sys.dm_exec_requests BlockingRequest
ON BlockingRequest.session_id = Request.blocking_session_id
LEFT JOIN sys.dm_exec_sessions Session
ON Session.session_id = BlockingRequest.session_id
LEFT JOIN sys.dm_exec_connections Connection
ON Connection.session_id = Session.session_id
OUTER APPLY sys.dm_exec_sql_text(BlockingRequest.sql_handle) as SqlText
WHERE Request.session_id > 50
AND Request.blocking_session_id <> 0
) as BlockingProcess
ORDER BY BlockingProcess.BlockedBy DESC, BlockingProcess.SPID
DECLARE BlockingProcessCursor CURSOR FAST_FORWARD FOR
SELECT SPID, CPUTime, IOWrites + IOReads as DiskIO,
Command, ObjectName, TransactionIsolation
FROM #ProcessRaw
GROUP BY SPID, CPUTime, IOWrites + IOReads, Command, ObjectName, TransactionIsolation
OPEN BlockingProcessCursor
FETCH NEXT FROM BlockingProcessCursor
INTO @BlockingSPID, @CPUTime, @DiskIO, @Command, @ObjectName, @TransactionIsolation
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TransactionCount = 0
IF @BlockingSPID = '-2'
BEGIN
DECLARE @UnitOfWork varchar(50)
select top 1 @UnitOfWork = ISNULL(req_transactionUOW, '')
from master..syslockinfo
where req_spid = -2
if @KillOrphanedProcesses = 1
BEGIN
if @UnitOfWork <> '' exec('KILL ''' + @UnitOfWork + '''')
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- Killed UOW ' + @UnitOfWork + ' -')
END
ELSE
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- Orphaned. UOW = ' + @UnitOfWork + ' -')
END
END
ELSE IF @BlockingSPID = '-3'
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- deferred recovery transaction -')
END
ELSE IF @BlockingSPID = '-4'
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- Latch owner could not be determined -')
END
ELSE
BEGIN
SELECT @TransactionCount = open_tran FROM master.sys.sysprocesses WHERE SPID=@BlockingSPID
IF @BlockingSPID <> 0
BEGIN
if @KillBlockingProcesses = 1
BEGIN
exec('KILL ' + @BlockingSPID)
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- Killed Process ' + @BlockingSPID + ' -')
END
ELSE
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
EXEC ('DBCC INPUTBUFFER(' + @BlockingSPID + ') WITH NO_INFOMSGS')
END
END
ELSE
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES ('', '', ' - Unable to determin PID - ')
END
END
UPDATE #BlockingProcess
SET ProcessID = @BlockingSPID,
CPUTime = @CPUTime,
DiskIO = @DiskIO,
TransactionCount = @TransactionCount,
Command = @Command,
ObjectName = @ObjectName,
TransactionIsolation = @TransactionIsolation
WHERE BlockingProcessID = SCOPE_IDENTITY()
FETCH NEXT FROM BlockingProcessCursor
INTO @BlockingSPID, @CPUTime, @DiskIO, @Command, @ObjectName, @TransactionIsolation
END
CLOSE BlockingProcessCursor
DEALLOCATE BlockingProcessCursor
SELECT ProcessID, EventInfo, Command, ObjectName, TransactionCount, TransactionIsolation,
CPUTime, DiskIO
FROM #BlockingProcess
SET NOCOUNT OFF
Chris Maunder is the co-founder of
CodeProject, DeveloperMedia and ContentLab, and has been a prominent figure in the software development community for nearly 30 years. Hailing from Australia, Chris has a background in Mathematics, Astrophysics, Environmental Engineering and Defence Research. His programming endeavours span everything from FORTRAN on Super Computers, C++/MFC on Windows, through to to high-load .NET web applications and Python AI applications on everything from macOS to a Raspberry Pi. Chris is a full-stack developer who is as comfortable with SQL as he is with CSS.
In the late 1990s, he and his business partner David Cunningham recognized the need for a platform that would facilitate knowledge-sharing among developers, leading to the establishment of CodeProject.com in 1999. Chris's expertise in programming and his passion for fostering a collaborative environment have played a pivotal role in the success of CodeProject.com. Over the years, the website has grown into a vibrant community where programmers worldwide can connect, exchange ideas, and find solutions to coding challenges. Chris is a prolific contributor to the developer community through his articles and tutorials, and his latest passion project,
CodeProject.AI.
In addition to his work with CodeProject.com, Chris co-founded ContentLab and DeveloperMedia, two projects focussed on helping companies make their Software Projects a success. While at CodeProject, Chris' roles included Architecture and coding, Product Development, Content Creation, Community Growth, Client Satisfaction and Systems Automation, and many, many sales meetings. All while keeping his sense of humour.