How to get spid of jobs that are running along with job name

  • Hi,

    Can you please let me know how to get spid of jobs that are running along with job name

    Thanks

  • sp_who2 ?

  • Filter sys.dm_exec_sessions where the program_name is like ‘%jobstep%’ and then take the binary value from the program name and pass that to sysjobs.

    The binary value is equal to the job_id so you would do something like

    select name from msdb.dbo.sysjobs where job_id =0x1234567890Abcdefghijmnopqrstuvwxyz

  • Building on what Ant-Green wrote, you can use this:

    select *
    from sys.dm_exec_sessions s
    inner join msdb.dbo.sysjobs j
    on convert(char(34),convert(binary(16),j.job_id),1) = substring(s.program_name, charindex('(Job 0x',s.program_name,1)+5,34)
    where s.program_name like 'SQLAgent%Jobstep%'
    and j.name='TEST' -- Your job name

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply