SQL Querying Job Activity Monitor for Status

Question

I'm trying to write a query to find the current status of a SQL Agent Job. This field exists under the "Status" column in Job Activity Monitor. See screenshot below:

I'm using the queries found on this site (query also below), which are great but the status it returns is the status of a job that has already been run.

Does anyone know of a way to query the current status vs. the status the job ended at? In the example above I would want to return "Executing 1(Run Query)". "Run Query" is the name of step 1 of the job.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , CASE 
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') 
        AS [LastRunDuration (HH:MM:SS)]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime]
FROM 
    [msdb].[dbo].[sysjobs] (NOLOCK) AS [sJOB]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules] (NOLOCK)
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT 
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id] 
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory] (NOLOCK)
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
WHERE [sJOB].[job_id] = '527BA180-A5D9-4492-98F0-705889EBCFC4'
ORDER BY [JobName]

Answer 1

The table which holds the status information is actually an extended stored procedure called xp_sqlagent_enum_jobs. It is accessed through sp_help_job which is documented here. If you call sp_help_job with no paramters, it will include the columns current_execution_status and current_execution_step which is what you are after.

If you do a little digging inside sp_help_job, you can see that xp_sqlagent_enum_jobs is called with the following parameters:

IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
    INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id
ELSE
    INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner

You can take this further, copy the structure of the temporary table and call it yourself so that you can then cherry pick the results:

declare @job_name sysname = 'fill in your jobname here';
declare @job_id uniqueidentifier = (select top 1 job_id from msdb..sysjobs where name = @job_name);
declare @job_owner sysname = (SELECT SUSER_SNAME());
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, -- 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);

INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

SELECT sj.name,
    case xpr.job_state when 1 then 'Executing: ' + cast(sjs.step_id as nvarchar(2)) + ' (' + sjs.step_name + ')'
        when 2  then 'Waiting for thread'
        when 3 then 'Between retries'
        when 4  then 'Idle'
        when 5  then 'Suspended'
        when 7  then 'Performing completion actions'
    end as [status]
FROM @xp_results                          xpr
inner join msdb..sysjobs sj on xpr.job_id = sj.job_id
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
    msdb.dbo.sysjobs_view                sjv
WHERE (sjv.job_id = xpr.job_id)

Note that if you just leave @job_name as null, it will return for all jobs.

Answer 2

USE msdb EXEC dbo.sp_help_job

execution_status is a parameter in the SP.

Value Description

• 0 Returns only those jobs that are not idle or suspended.
• 1 Executing.
• 2 Waiting for thread.
• 3 Between retries.
• 4 Idle.
• 5 Suspended.
• 7 Performing completion actions.

exec msdb.dbo.sp_help_job @execution_status = 1

Answer 3

I normally use this query to check job run status, are you looking this? This is from msdn as well

SELECT sj.Name, 
CASE
    WHEN sja.start_execution_date IS NULL THEN 'Not running'
    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);