I have a blocking alert in place that sends me the following data regarding the blocker if the blocker is a job.
SQLAgent – TSQL JobStep (Job 0xECBCC3DD8EE8E8458CF19641D0EA2EB4 : Step 1)
I also include the command executed by the step and some addition details like database, all useful information. What about drilling into the job specifics? Below is a piece of sql code that will resolve the job name, and job step details for a specific active job or all currently running jobs by job_id. This makes tracking down the job step specifics easier.
‘SQLAgent – TSQL JobStep (Job 0x’+
CONVERT(char(32),CAST(j.job_id AS binary(16)),2) +
‘ : Step ‘ +
CAST(js.step_id AS VARCHAR(3)) +
‘)’, j.job_id, j.name, js.step_name, js.database_name, js.command
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js ON j.job_id = js.job_id
Where CONVERT(char(32),CAST(j.job_id AS binary(16)),2) like ‘EC%’
Use without the where clause to get all active job steps
Use with the like to narrow the scope of jobs: Where CONVERT(char(32),CAST(j.job_id AS binary(16)),2) like ‘EC%’
Use = and take the 32 characters after the 0x to get the active steps for a specific job: Where CONVERT(char(32),CAST(j.job_id AS binary(16)),2) = ‘ECBCC3DD8EE8E8458CF19641D0EA2EB4′
As with any script you get off the internet please review for appropriateness in your environment. NJOY and I hope this sample code is useful.