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.
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:
System Table - msdb.dbo.sysjobservers
- an indicator showing if the job is running
- the currently running step id (if running)
- the job step status (if running)
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.
DECLARE @Job_ID as varchar(100)
SET @Job_ID = '%'
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,
request_source int NOT NULL,
COLLATE database_default NULL,
running int NOT NULL,
current_step int NOT NULL,
current_retry_attempt int NOT NULL,
job_state int NOT NULL)
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';
job.name as Job_Name,
(select top 1 start_execution_date
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
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,
WHEN r.running = 0 then jobinfo.last_run_outcome
WHEN r.job_state = 0 THEN 1 WHEN r.job_state = 4 THEN 1
WHEN r.job_state = 5 THEN 1
WHEN r.job_state = 1 THEN 2 WHEN r.job_state = 2 THEN 2
WHEN r.job_state = 3 THEN 2
WHEN r.job_state = 7 THEN 2
END as Run_Status,
WHEN r.running = 0 then
WHEN jobInfo.last_run_outcome = 0 THEN 'Failed'
WHEN jobInfo.last_run_outcome = 1 THEN 'Success'
WHEN jobInfo.last_run_outcome = 3 THEN '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.
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
where job_id = @Job_ID
order by start_execution_date desc)
FROM msdb.dbo.sysjobsteps AS Jobstep
WHERE job_id = @Job_ID) AS Steps LEFT JOIN
CASE 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
END AS run_status,
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'
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.