Getting SPID for an SQL Server Agent Job
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