Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

View SQL Server Job Status using SQL Reporting Services

Rate me:
Please Sign up or sign in to vote.
4.78/5 (8 votes)
30 Jul 2012CPOL3 min read 72.9K   2.4K   15   17
Create a SQL Reporting Services report to show the status of all SQL Jobs and drill down to the status of the steps within each job.

Sample Image

Introduction

Have you ever wanted to view a quick summary of the status of your SQL Server Jobs, or even allow others without access to SQL Server view the status as well? SQL Server Reporting Services is the perfect tool to do just that. You can quickly create a report with indicators so you can see at a glance how all of your jobs are doing. We have one job in particular with about 20 steps that routinely runs behind schedule and fails occasionally. So, I wanted a way to quickly view its status and drill down to the status of each of its steps to see where it is in its execution or to see which step failed. Also, by using SSRS, we can allow other people such as IT Managers and end users to check on the status of the jobs important to them.

Background

The inspiration for this article came from here: JOB Running Status Report in SSRS. But, that article only shows a high level job status, not a drill down to the steps within each job. Also, I found the SQL to be a bit confusing. So, I decided to create this article to show how I added on to the SSRS reports and simplify the SQL (at least I hope it is simpler).

Using the code

I am not going to go through the steps to create the reports in this article. You can download the SQL Reporting Services files. They are like any other SSRS report; you provide a data source and then design the report accordingly. The part I want to focus on in this article is how to you get the job status data for the SSRS reports.

Finding job status data

The details about a job's status can be found using a few different system tables and store procedures in SQL Server. Here is a quick list with some description:

  • Stored Procedure - master.dbo.xp_sqlagent_enum_jobs
  • This stored procedure will give you info about currently running jobs. However, it is not reliable for completed jobs. I use it to see the overall status of a running job and each step. From this table I pull:

    1. an indicator showing if the job is running
    2. the currently running step id (if running)
    3. the job step status (if running)
  • System Table - msdb.dbo.sysjobservers
  • This table is useful to see the final status of a completed job and each step. It will show you when a job/step failed, succeeded, or was canceled. However, note that this table contains NO information on currently running jobs. If a job is running, this table will only have data for the prior run.

  • System Table - msdb.dbo.sysjobs
  • This table will give you information about the job (nothing related to job execution history). I use it to find the job name.

  • System Table - msdb.dbo.sysjobactivity
  • This table basically logs the execution history of the jobs. When a job is started, a new record is added. When the job completed, the execution details are updated. I use this table to find the most recent start date and time (even if it is currently running). Also, using the stop execution date (or the current system time if the job is still running), I can calculate how long the job ran.

Queries for job status data

This query I use to find the status for all enabled jobs. This is used for the main job status report.

SQL
DECLARE @Job_ID as varchar(100)
SET @Job_ID = '%' -- you can specify a job id to query for a certain job

CREATE TABLE #JobResults
    (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    #JobResults 
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';

SELECT    
    r.job_id, 
    job.name as Job_Name, 
    
    (select top 1 start_execution_date 
            FROM [msdb].[dbo].[sysjobactivity]
            where job_id = r.job_id
            order by start_execution_date desc) as Job_Start_DateTime,
            
    cast((select top 1 ISNULL(stop_execution_date, GETDATE()) - start_execution_date  
            FROM [msdb].[dbo].[sysjobactivity]
            where job_id = r.job_id
            order by start_execution_date desc) as time) as Job_Duration, 
            
    r.current_step AS Current_Running_Step_ID,
    CASE 
        WHEN r.running = 0 then jobinfo.last_run_outcome
        ELSE
            --convert to the uniform status numbers (my design)
            CASE
                WHEN r.job_state = 0 THEN 1    --success
                WHEN r.job_state = 4 THEN 1
                WHEN r.job_state = 5 THEN 1
                WHEN r.job_state = 1 THEN 2    --in progress
                WHEN r.job_state = 2 THEN 2
                WHEN r.job_state = 3 THEN 2
                WHEN r.job_state = 7 THEN 2
            END
    END as Run_Status,
    CASE 
        WHEN r.running = 0 then 
            -- convert to the uniform status numbers (my design)
            -- no longer running, use the last outcome in the sysjobservers
            -- sysjobservers will give last run status, but does not know about current running jobs
            CASE 
                WHEN jobInfo.last_run_outcome = 0 THEN 'Failed'
                WHEN jobInfo.last_run_outcome = 1 THEN 'Success'
                WHEN jobInfo.last_run_outcome = 3 THEN 'Canceled'
                ELSE 'Unknown'
            end
            -- convert to the uniform status numbers (my design)
            -- if running, use the job state in xp_sqlagent_enum_jobs    
            -- xp_sqlagent_enum_jobs will give current status, but does not know if a completed job
            -- succeeded, failed or was canceled.
            WHEN r.job_state = 0 THEN 'Success'
            WHEN r.job_state = 4 THEN 'Success'
            WHEN r.job_state = 5 THEN 'Success'
            WHEN r.job_state = 1 THEN 'In Progress'
            WHEN r.job_state = 2 THEN 'In Progress'
            WHEN r.job_state = 3 THEN 'In Progress'
            WHEN r.job_state = 7 THEN 'In Progress'
         ELSE 'Unknown' END AS Run_Status_Description
FROM    #JobResults as r left join
        msdb.dbo.sysjobservers as jobInfo on r.job_id = jobInfo.job_id inner join
        msdb.dbo.sysjobs as job on r.job_id = job.job_id 
WHERE    cast(r.job_id as varchar(100)) like @Job_ID
        and job.[enabled] = 1
order by job.name
        
DROP TABLE #JobResults

Queries for job status data

This query I use to find the status of each step for a specific job. This is used for the drill down report.

SQL
DECLARE @Job_ID as uniqueidentifier
SET @Job_ID = '<enter your job_id here>'

DECLARE @Job_Start_DateTime as smalldatetime

SET @Job_Start_DateTime = (select top 1 start_execution_date 
            FROM [msdb].[dbo].[sysjobactivity]
            where job_id = @Job_ID
            order by start_execution_date desc)

SELECT         
    Steps.step_id, 
    Steps.step_name, 
    run_status, 
    run_status_description, 
    Step_Start_DateTime,
    Step_Duration
FROM            
    (SELECT        
        Jobstep.step_name, 
        Jobstep.step_id
    FROM    msdb.dbo.sysjobsteps AS Jobstep
    WHERE job_id = @Job_ID) AS Steps LEFT JOIN
    
    (SELECT
         JobHistory.step_id, 
         CASE --convert to the uniform status numbers we are using
            WHEN JobHistory.run_status = 0 THEN 0
            WHEN JobHistory.run_status = 1 THEN 1
            WHEN JobHistory.run_status = 2 THEN 2
            WHEN JobHistory.run_status = 4 THEN 2
            WHEN JobHistory.run_status = 3 THEN 3
            ELSE 'Unknown' 
         END AS run_status, 
         CASE 
            WHEN JobHistory.run_status = 0 THEN 'Failed' 
            WHEN JobHistory.run_status = 1 THEN 'Success' 
            WHEN JobHistory.run_status = 2 THEN 'In Progress'
            WHEN JobHistory.run_status = 4 THEN 'In Progress' 
            WHEN JobHistory.run_status = 3 THEN 'Canceled' 
            ELSE 'Unknown' 
         END AS run_status_description,
         CAST(STR(run_date) AS DATETIME) + CAST(STUFF(STUFF(REPLACE(STR(run_time, 6, 0), ' ', 
              '0'), 3, 0, ':'), 6, 0, ':') AS TIME) as Step_Start_DateTime,
         CAST(CAST(STUFF(STUFF(REPLACE(STR(JobHistory.run_duration % 240000, 6, 0), ' ', '0'), 
              3, 0, ':'), 6, 0, ':') AS DATETIME) AS TIME) AS Step_Duration
    FROM msdb..sysjobhistory as JobHistory WITH (NOLOCK) 
    WHERE job_id = @Job_ID and CAST(STR(run_date) AS DATETIME) + 
        CAST(STUFF(STUFF(REPLACE(STR(run_time, 6, 0), ' ', '0'), 
                   3, 0, ':'), 6, 0, ':') AS TIME) >= @Job_Start_DateTime
    ) AS StepStatus ON Steps.step_id = StepStatus.step_id
ORDER BY Steps.step_id

Points of Interest

Creating these SSRS reports was a great experience to learn more about how to find information in SQL Server's master database. Hopefully you will find this interesting too and it may inspire you to dig more into how SQL Server.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionChanges for Sql 2012+ Pin
Dont_Worry28-Jul-16 3:27
Dont_Worry28-Jul-16 3:27 
SuggestionSQL2012 Changes Pin
DBAFahim22-Mar-16 8:03
DBAFahim22-Mar-16 8:03 
Questiondata types datetime and time are incompatible in the add operator error Pin
Member 1097073125-Jul-14 5:02
Member 1097073125-Jul-14 5:02 
AnswerRe: data types datetime and time are incompatible in the add operator error Pin
mgoad9925-Jul-14 5:16
mgoad9925-Jul-14 5:16 
GeneralRe: data types datetime and time are incompatible in the add operator error Pin
Member 1097073127-Jul-14 22:17
Member 1097073127-Jul-14 22:17 
GeneralRe: data types datetime and time are incompatible in the add operator error Pin
mgoad9928-Jul-14 4:19
mgoad9928-Jul-14 4:19 
GeneralRe: data types datetime and time are incompatible in the add operator error Pin
Member 1097073114-Aug-14 6:15
Member 1097073114-Aug-14 6:15 
GeneralRe: data types datetime and time are incompatible in the add operator error Pin
Member 150934228-Mar-21 6:41
Member 150934228-Mar-21 6:41 
QuestionSupported SQL Server Versions Pin
WilRogJr18-Feb-14 3:56
WilRogJr18-Feb-14 3:56 
QuestionWhat is Name of DataSources Pin
Member 102473032-Sep-13 3:01
Member 102473032-Sep-13 3:01 
QuestionQuestion Pin
Member 1011161021-Aug-13 15:41
Member 1011161021-Aug-13 15:41 
AnswerRe: Question Pin
mgoad9928-Aug-13 11:18
mgoad9928-Aug-13 11:18 
QuestionSuper code Pin
danielparis@hotmail.com27-Jan-13 22:51
danielparis@hotmail.com27-Jan-13 22:51 
GeneralMy vote of 5 Pin
ii_noname_ii30-Jul-12 23:51
ii_noname_ii30-Jul-12 23:51 
QuestionMessage Closed Pin
30-Jul-12 15:06
jlufisasd30-Jul-12 15:06 
GeneralRe: ++++ http://www.netetrader.com ++++++++++ Best online store Pin
ii_noname_ii30-Jul-12 23:45
ii_noname_ii30-Jul-12 23:45 
GeneralMy vote of 4 Pin
Christian Amado30-Jul-12 9:10
professionalChristian Amado30-Jul-12 9:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.