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?

    SELECT sj.name, 
     sja.run_requested_date, 
     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.

    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;
    

    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.

    INSERT INTO #Job
    EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo
    

    And the table to load it into:

    CREATE TABLE #Job 
               (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
    j.name AS 'JobName',
    run_date,
    run_time,
    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 http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2/Detecting-The-State-of-a-SQL-Server-Agent-Job.htm
        CREATE TABLE #ENUM_JOB 
        ( 
            Job_ID UNIQUEIDENTIFIER, 
            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 
        )
        INSERT INTO #ENUM_JOB 
             EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage
        SELECT E.*, SJ.name 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
        DROP TABLE #ENUM_JOB
    
  • The following script returns:

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

    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 (
        SELECT 
            ja.job_id
            ,j.name
            ,ja.start_execution_date
            ,ja.last_executed_step_id
            --,j.start_step_id
            ,CASE 
                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
            ,ja.job_history_id
            ,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
        )
    
    SELECT 
        jd.job_id
        ,jd.name
        ,jd.start_execution_date 
        ,COALESCE(
                    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
                                        END
                            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
                    END,jd.ExecutingStepId) AS StepId
        ,jd.JobHourRunning
        ,jd.JobMinuteRunning
        ,jd.JobSecondRunning                                
    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