When troubleshooting of an incident, it is important to find out SPID attached to the SQL Server Agent job, so that you can find the wait stat and other relevant information. Also, sometimes you may need to kill the SPID.
There are a DMV and a system proc you can get the help to achieve above task.
msdb..sp_help_job
@execution_status = 1
The above query will give you the list of jobs running now along with other properties of the job.
1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions are the other options you can provide to list the SQL Server Agent jobs with different execution statues.
Job information can also be retrieved from the msdb.dbo.sysjobs DMV. However, that DMV does not have the information about current execution statues.
SPID information can be retrieved from the master.dbo.sysprocesses DMV.
This DMV produces the below results for the agent jobs.
Above results has the spid and the SQL Server Agent Job guid and the step.
By combining these two dmvs, we can get the following query.
SELECT p.spid, j.name as JobName,SUBSTRING(PROGRAM_NAME,67,LEN(PROGRAM_NAME) - 67) JobStep FROM master.dbo.sysprocesses p INNER JOIN msdb.dbo.sysjobs j ON master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), job_id)) = SUBSTRING(REPLACE(PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)
This will produce the below results.
