Blocking Chain
Published: Apr 21, 2014
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
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron