Blocking Chain
Published: 3rd May 2013
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
Comments:
NB: Comments will only appear once they have been moderated.