Script to see running jobs in SQL Server with Job Start Time

  • how to write script to see running jobs in SQL Server with Job Start Time?

     CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
    FROM msdb.dbo.sysjobactivity sja
    INNER JOIN msdb.dbo.sysjobs sj
    ON sja.job_id = sj.job_id
    WHERE sja.run_requested_date IS NOT NULL
    ORDER BY sja.run_requested_date desc;

    Not sure I understand the question. Do you only want to see jobs that are currently running?

  • I posted a query a while back for getting a list of currently running jobs here.

        ja.job_id, AS job_name,
        ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    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;

    This has job name, job id, start time and what step it's running on. If you follow the link above you can get some more detail on how I came up with it and what else is available.

    Unfortunately, this script assumes that the currently running step is the one after the last completed step. This isn't always the case.

  • I ran into issues on one of my servers querying MSDB tables (aka code listed above) as one of my jobs would come up running, but it was not. There is a system stored procedure that returns the execution status, but one cannot do a insert exec statement without an error. Inside that is another system stored procedure that can be used with an insert exec statement.

    EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo

    And the table to load it into:

               (job_id               UNIQUEIDENTIFIER NOT NULL,  
               last_run_date         INT              NOT NULL,  
               last_run_time         INT              NOT NULL,  
               next_run_date         INT              NOT NULL,  
               next_run_time         INT              NOT NULL,  
               next_run_schedule_id  INT              NOT NULL,  
               requested_to_run      INT              NOT NULL, -- BOOL  
               request_source        INT              NOT NULL,  
               request_source_id     sysname          COLLATE database_default NULL,  
               running               INT              NOT NULL, -- BOOL  
               current_step          INT              NOT NULL,  
               current_retry_attempt INT              NOT NULL,  
               job_state             INT              NOT NULL) 
  • This will give you the job name and run date and time, you can remove the date if you just want to see the name and time it runs.

    SELECT AS 'JobName',
    msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'
    FROM msdb.dbo.sysjobs j 
    JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id 
    WHERE j.enabled = 1  --Only Enabled Jobs
    ORDER BY JobName, RunDateTime desc

    +1 for msdb.dbo.agent_datetime(run_date, run_time)

  • This script will show the jobs currently running, as well as the step currently being executed:

    -- From
            Last_Run_Date INT, 
            Last_Run_Time INT, 
            Next_Run_Date INT, 
            Next_Run_Time INT, 
            Next_Run_Schedule_ID INT, 
            Requested_To_Run INT, 
            Request_Source INT, 
            Request_Source_ID VARCHAR(100), 
            Running INT, 
            Current_Step INT, 
            Current_Retry_Attempt INT, 
            State INT 
             EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage
        SELECT E.*, AS job_name, SJS.step_name
            FROM #ENUM_JOB AS E
            JOIN msdb.dbo.sysjobs AS SJ
                ON SJ.job_id = E.Job_ID
            JOIN msdb.dbo.sysjobsteps AS SJS
                ON SJS.job_id = SJ.job_id
                AND SJS.step_id = E.Current_Step
  • The following script returns:

    • Job name
    • Job id
    • Job requested time
    • Job execution time
       , 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

    For me this query returned disabled jobs that aren't actually running

    The answer by @Kenneth is better as it returns currently running jobs only.

  • It's a little around the houses possibly but does the trick

    ;WITH cJobDetail AS (
                WHEN j.start_step_id = COALESCE(ja.last_executed_step_id,j.start_step_id) THEN j.start_step_id
                ELSE NULL
                END AS ExecutingStepId
            ,DATEDIFF(HOUR, ja.start_execution_date, COALESCE(ja.stop_execution_date,SYSDATETIME())) AS JobHourRunning
            ,DATEDIFF(SECOND, ja.start_execution_date, COALESCE(ja.stop_execution_date,SYSDATETIME())) / 60 % 60 AS JobMinuteRunning
            ,DATEDIFF(SECOND, ja.start_execution_date, COALESCE(ja.stop_execution_date,SYSDATETIME())) % 60 AS JobSecondRunning 
        FROM dbo.sysjobactivity ja
        INNER JOIN dbo.sysjobs j
            ON ja.job_id = j.job_id
        WHERE ja.start_execution_date IS NOT NULL
        AND ja.stop_execution_date IS NULL
    ,MaxJobHistoryStepStatus AS (
        SELECT jh.job_id, jh.step_id, MAX(jh.instance_id) AS MaxInstanceId
        FROM cJobDetail jd
        INNER JOIN dbo.sysjobhistory jh
            ON jd.job_id = jh.job_id
            AND jd.last_executed_step_id = jh.step_id
        GROUP BY jh.job_id, jh.step_id
                    CASE jh.run_status 
                            WHEN 1 THEN 
                                        CASE js.on_success_action
                                                                WHEN 3 THEN jd.last_executed_step_id + 1
                                                                WHEN 4 THEN js.on_success_step_id
                            WHEN 0 THEN 
                                        CASE js.on_fail_action
                                                            WHEN 3 THEN jd.last_executed_step_id + 1
                                                            WHEN 4 THEN js.on_fail_step_id
                    END,jd.ExecutingStepId) AS StepId
    FROM cJobDetail jd
    LEFT JOIN MaxJobHistoryStepStatus mhs
        ON jd.job_id = mhs.job_id
    LEFT JOIN dbo.sysjobhistory jh
        ON mhs.MaxInstanceId = jh.instance_id
    LEFT JOIN dbo.sysjobsteps js
        ON mhs.job_id = js.job_id
        AND mhs.step_id = js.step_id

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM