none
Return the name of the Current Running SQL Agent Job RRS feed

  • Question

  • Is there a way in a SQL Agent Job to retrieve the current job name without using tokens?

    Thanks in advance

    Wednesday, January 22, 2020 9:15 PM

All replies

  • you might try this:

    SELECT sj.name
       , sja.*
    FROM msdb.dbo.sysjobactivity AS sja
    INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
    WHERE sja.start_execution_date IS NOT NULL
       AND sja.stop_execution_date IS NULL

    from

    https://stackoverflow.com/questions/18445825/how-to-know-status-of-currently-running-jobs

    Wednesday, January 22, 2020 9:24 PM
  • Hi miguelh, 

    This has job name, job id, start time and what step it's running on. 

    SELECT
        ja.job_id,
        j.name AS job_name,
        ja.start_execution_date,      
        ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
        Js.step_name
    FROM msdb.dbo.sysjobactivity ja 
    LEFT JOIN msdb.dbo.sysjobhistory jh 
        ON ja.job_history_id = jh.instance_id
    JOIN msdb.dbo.sysjobs j 
    ON ja.job_id = j.job_id
    JOIN msdb.dbo.sysjobsteps js
        ON ja.job_id = js.job_id
        AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
    WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
    AND start_execution_date is not null
    AND stop_execution_date is null;

    Also , this link will provide you more information . A T-SQL query to get current job activity

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 23, 2020 2:30 AM