Sql-server – Measure Agent Job failure and running jobs with ‘execution_status’

If you're willing to use an undocumented and, therefore, unsupported extended stored procedure, you can get the current execution stats from msdb.dbo.xp_sqlagent_enum_jobs.

I use the following to get details about running jobs:

DECLARE @can_see_all_running_jobs int = 1;
DECLARE @job_owner sysname = 'sa';
DECLARE @job_id uniqueidentifier;

DECLARE @job_states TABLE
(
    job_state_id int NOT NULL
    , job_state_desc varchar(30) NOT NULL
);

INSERT INTO @job_states (job_state_id, job_state_desc)
VALUES (0, 'Not idle or suspended')
    , (1, 'Executing')
    , (2, 'Waiting for Thread')
    , (3, 'Between Retries')
    , (4, 'Idle')
    , (5, 'Suspended')
    , (6, 'Waiting for Step to Finish')
    , (7, 'Performing Completion Actions');

DECLARE @xp_results TABLE 
(
      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
    , request_source        INT              NOT NULL
    , request_source_id     sysname          COLLATE database_default NULL
    , running               INT              NOT NULL
    , current_step          INT              NOT NULL
    , current_retry_attempt INT              NOT NULL
    , job_state             INT              NOT NULL
);
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 
    @can_see_all_running_jobs
    , @job_owner
    , @job_id;

SELECT 
    JobName = j.name
    , states.job_state_desc
    , LastRunDateTime = CASE 
        WHEN COALESCE(xr.last_run_date, 0) > 0 
            THEN msdb.dbo.agent_datetime(xr.last_run_date, xr.last_run_time) 
        ELSE NULL 
        END
    , xr.current_step
    , step_name = (
        SELECT '' + sjs.step_name 
        FROM dbo.sysjobsteps sjs 
        WHERE sjs.job_id = j.job_id 
            AND sjs.step_id = xr.current_step 
        FOR XML PATH ('')
        )
FROM msdb.dbo.sysjobs j 
    INNER JOIN @xp_results xr ON j.job_id = xr.job_id
    LEFT JOIN @job_states states on xr.job_state = states.job_state_id
WHERE j.enabled = 1 /* enabled jobs only */
    AND xr.running = 1 /* running jobs only */
ORDER BY j.name;

This shows results like:

╔══════════╦════════════════╦═════════════════════════╦══════════════╦═══════════╗
║ JobName  ║ job_state_desc ║     LastRunDateTime     ║ current_step ║ step_name ║
╠══════════╬════════════════╬═════════════════════════╬══════════════╬═══════════╣
║ Test Job ║ Executing      ║ 2018-04-10 12:40:09.000 ║            1 ║ Step 1    ║
╚══════════╩════════════════╩═════════════════════════╩══════════════╩═══════════╝

Items of note:

1. The "last run date" columns only reflect the time the job was last started. i.e. this does NOT indicate the start of the job if the job is currently running. There appears to be no way to get that data from objects contained in msdb. I do have a way to get those details from sys.dm_exec_sessions, etc, as in:

   /*
         Shows the progress of any running SQL Agent jobs
   */
   DECLARE @Actions TABLE
   (
         ActionID INT
         , ActionText VARCHAR(50)
   );
   INSERT INTO @Actions (ActionID, ActionText)
   VALUES ( 1,'Quit with success')
   
   INSERT INTO @Actions (ActionID, ActionText)
   VALUES (2,'Quit with failure')
   
   INSERT INTO @Actions (ActionID, ActionText)
   VALUES (3,'Go to next step')
   
   INSERT INTO @Actions (ActionID, ActionText)
   VALUES (4, 'Go to step on_success_step_id');
   
   SELECT [Job Name] = sj.name
         , [Step Name] = sjs.step_name 
         , SQLStatement = SUBSTRING(t.text, ISNULL(r.statement_start_offset / 2 + 1,0), CASE WHEN ISNULL(r.statement_end_offset, 0) = -1 THEN LEN(t.text) ELSE ISNULL(r.statement_end_offset / 2, 0) END - ISNULL(r.statement_start_offset / 2, 0)) 
         , [On Success Action] = ASuccess.ActionText 
         , [On Fail Action] = AFail.ActionText 
         , r.session_id
         , r.blocking_session_id
         , [Estimated Completion] = CASE WHEN r.estimated_completion_time = 0 THEN 'UNKNOWN' ELSE CONVERT(VARCHAR(50), DATEADD(MILLISECOND, r.estimated_completion_time, GETDATE()), 120) END
         , Duration = DATEDIFF(MINUTE, r.start_time, GETDATE())
         , r.last_wait_type
         , r.start_time
         , s.host_name
         , SQLText = t.text
         , s.program_name
   FROM sys.dm_exec_sessions s WITH (NOLOCK)
         LEFT JOIN sys.dm_exec_requests r WITH (NOLOCK)on s.session_id = r.session_id
         CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t 
         LEFT JOIN msdb.dbo.sysjobsteps sjs WITH (NOLOCK) ON SUBSTRING(s.[program_name],30,34) = master.dbo.fn_varbintohexstr(sjs.job_id) AND SUBSTRING(s.[program_name], 72, LEN(s.[program_name]) - 72) = SJS.step_id
         LEFT JOIN msdb.dbo.sysjobs sj (NOLOCK) ON sjs.job_id = sj.job_id
         LEFT JOIN @Actions AFail ON sjs.on_fail_action = AFail.ActionID
         LEFT JOIN @Actions ASuccess ON sjs.on_success_action = ASuccess.ActionID
   WHERE s.program_name like 'SQLAgent - TSQL%';
   

2. Limiting selected jobs with msdb.dbo.agent_datetime(run_date, run_time) > DATEADD(DAY,-90, GETDATE()) in the WHERE clause means currently running jobs that have never been ran before will not show up, and will return an error for jobs that have never been ran.

I've tied all the above details into a single package, and made it available on my blog.