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.

    @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.

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


No comments yet... Be the first to leave a reply!