Detect head blocker and how to retrieve job step name or job name for a SQL server Agent job.

What will you do if you get blocking sessions:-

using below script  check head blocker
---------------------------------------------------

SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
---------------------------------


Get the Head blocker session id from above query..

Then execute

select program_name from sys.sysprocesses where spid = <sessionid>

ex:-
select program_name from sys.sysprocesses where spid =  70

if program_name is like -- SQLAgent - TSQL JobStep (Job 0x8F2B94F4E17DFC44AE94F157CE24C324 : Step 1)


Then execute below command

select * from msdb.dbo.sysjobsteps where job_id = 0x8F2B94F4E17DFC44AE94F157CE24C324
-- step_name =<It will show step_name of the job>

get the job_id from above command and execute below command to get job_name.

select * from msdb.dbo.sysjobs where job_id = 'F4942B8F-7DE1-44FC-AE94-F157CE24C324' -- Reorganize indexes in the XXX table.


Check the job, what it is doing.
Take approval from your customer and take appropriate action.
if customer says to cancel / kill the job, kill the job.

and then execute above head blocker script.

or run

select program_name from sys.sysprocesses where spid =  70 -- it  will return no row(s) found.

in this way we identify head blocker for a session id and find other details related to sql server agent job.

Thanks for reading.