Measure Agent Job failure and running jobs with 'execution_status'

Question

Considerations

• I need to create a Dashboard with job failure AND running jobs.
• I have a code to get run_status etc.
• I am not interested in succeed jobs.
• I extract failure from run_status = 0.

Query as of today:

select 
   j.name as 'JobName',run_date, run_time, run_status,
   msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
   run_duration,
   ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100
+ 31 ) / 60) as 'RunDurationMinutes',
   DateADD(MINUTE,((run_duration/10000*3600 + (run_duration/100)%100*60 +    
run_duration%100 + 31 ) / 60) , 
   msdb.dbo.agent_datetime(run_date, run_time)) as RunStopDate, 
   step_id, Step_name
From 
   msdb.dbo.sysjobs j 
INNER JOIN 
   msdb.dbo.sysjobhistory h 
ON 
   j.job_id = h.job_id 
where 
   j.enabled = 1  --Only Enabled Jobs
and 
   msdb.dbo.agent_datetime(run_date, run_time)  > DATEADD(DAY,-90, GETDATE())

1. How and where do I add a case to below query? I want all my columns but also a new execeution_status as below.
2. I also assume I can get from run_duration the amount an on-going job has run so far.

    else 
    CASE h.execution_status
    WHEN j.[execution_status] = 0 THEN ' Returns only those jobs that are not 
    idle or suspended. '
    WHEN j.[execution_status] = 1 THEN 'Execution'
    WHEN j.[execution_status] = 2 THEN ' Waiting for thread '
    WHEN j.[execution_status] = 3 THEN ' Between retries '
    WHEN j.[execution_status] = 4 THEN 'Idle'
    WHEN j.[execution_status] = 5 THEN 'Suspenden'
    WHEN j.[execution_status] = 7 THEN 'Performing complet actions'
    End 
    AS ExecutionStatus, 

Source execution_status: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-job-transact-sql.

We did came up with this simple solution, and I want some feedback about pros and cons.

    SELECT
    ja.session_id,                
    ja.job_id,
    j.name AS job_name,
            CASE
        WHEN ja.start_execution_date IS NULL THEN 'Not running' 
        WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date 
    IS NULL THEN 'Running' 
        WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date 
    IS NOT NULL THEN 'Finished' 
    END AS 'RunStatus'  ,    
    ja.last_executed_step_id,     
    jh.step_name,  
    jh.run_status,
    ja.start_execution_date,      
    ja.stop_execution_date,       
    ja.job_history_id,
    j.[enabled]  
    FROM
    (msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON    
    ja.job_history_id = jh.instance_id) 
     join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id 
            WHERE session_id = (SELECT MAX(session_id) FROM 
     msdb.dbo.sysjobactivity) 
            and j.enabled =1 

Answer 1

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.