There are numerous variants of sp_who2 kicking around the internet and most of them go to absurd levels of intricacy.
For my situation, all I wanted was something that would show you a blocking chain and would also translate the SQL Server Agent Job name into plain English. That was all.
Therefore I came up with the following. It’s simple to use… just execute “sp_who4”. If you want a blocking chain then it’s “sp_who4 1”, and if you want to track an individual spid (eg. 144), then it’s simply “sp_who4 @spid=144”.
use master
go
create procedure [dbo].[sp_who4]
(
@block bit = 0,
@spid smallint = null
)
as
set nocount on
declare @spidTable table
(
spid smallint,
status varchar(100),
login varchar(100),
hostName varchar(100),
blockedBy varchar(10),
dbName varchar(100),
command varchar(100),
cpuTime int,
diskIO int,
lastBatch smalldatetime,
programName varchar(100),
spid2 smallint,
requestID bit
)
if @spid is null
begin
insert into @spidTable
select spid, [status], loginame, hostName,
case blocked when 0 then ' .' else convert(varchar, blocked) end,
db_Name(dbID), cmd, cpu, physical_io,
last_batch, [program_name], spid, 0
from master.dbo.sysprocesses with (nolock)
end
else
begin
set @block = 0
insert into @spidTable
select spid, [status], loginame, hostName,
case blocked when 0 then ' .' else convert(varchar, blocked) end,
db_Name(dbID), cmd, cpu, physical_io,
last_batch, [program_name], spid, 0
from master.dbo.sysprocesses with (nolock)
where spid = @spid
end
if @block = 0
begin
delete from @spidTable
where spid <= 50
end
declare @job varchar(100), @jobID varchar(100), @spid2 smallint
set @spid2 =
(
select distinct min(spid)
from @spidTable
where programName like 'SQLAgent - TSQL JobStep (%'
)
while @spid2 is not null
begin
set @job = (select distinct programName from @spidTable where spid = @spid2)
set @job = replace(@job, '(Job 0x', '(0x')
set @jobID = replace(@job, 'SQLAgent - TSQL JobStep (', '')
if (select charindex(' ', @jobID, 1)) > 0
begin
set @jobID = substring(@jobID, 1, charindex(' ', @jobID, 1)-1)
end
set @job = replace(@job, @jobID,
(select distinct name
from msdb.dbo.sysjobs
where master.dbo.fn_varbintohexstr(job_id) = @jobID))
update @spidTable
set programName = @job
where spid = @spid2
set @spid2 = (select distinct min(spid)
from @spidTable
where programName like 'SQLAgent - TSQL JobStep (%'
and spid > @spid2)
end
if @block = 1
begin
with blocked
(
blockpath,
spid,
[status],
[login],
hostname,
blockedBy,
dbname,
command,
cputime,
diskio,
lastbatch,
programname,
spid2,
requestid
)
as
(
select cast('' as varchar(max)), spid, status, login, hostname, blockedBy,
dbname, command, cputime, diskio, lastbatch,
programname, spid2, requestid
from @spidTable
where blockedBy = ' .'
and cast(spid as varchar) in
(select blockedBy from @spidTable)
union all
select b.blockpath + '.' + cast(b.spid as varchar),
w.spid, w.status, w.login, w.hostname,
w.blockedBy, w.dbname, w.command, w.cputime, w.diskio,
w.lastbatch, w.programname, w.spid2, w.requestid
from @spidTable w
inner join blocked b
on cast(b.spid as varchar) = w.blockedBy
and (b.blockpath not like ('%.' + cast(w.spid as varchar) + '.%')
or b.blockpath not like ('%.' + cast(w.spid as varchar)))
)
select distinct blockpath, spid, status, login, hostname, blockedBy,
dbName, command, cpuTime, diskIO, lastBatch, b.programName
from blocked b
end
else
begin
select spid, status, login, hostName, blockedBy, dbName, command,
cpuTime, diskIO, lastBatch, programName
from @spidTable
end
set nocount off