Post reply

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

  • August 5, 2021 at 9:18 am

    Go to Answer

    #3917083

    Hi,

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

    Thanks

  • August 5, 2021 at 4:35 pm

    #3917224

    sp_who2 ?

  • August 5, 2021 at 4:44 pm

    Answer

    #3917228

    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

  • August 6, 2021 at 11:34 am

    #3917453

    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 3 (of 3 total)

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