Viewing SQL Agent Job History and Duration visually using floating bars

Viewing SQL Agent Job History and Duration visually using floating bars. The title only tells half the story. What this article will describe is this and then applying this view across a number of inter-related servers. The gem though, is the last bit, reverse engineering the Job schedule based on the new found view that was previously not visible. So you may see that there is a peak period between 4am and 7am where a number of jobs are running and some jobs in this period may be moved earlier or later. I can click on the job and change the schedule, all from reporting services. ;-).

The intro kinda gives you a good idea of the contents of this blog. Basically, when viewing performance and what happens when on which server an important place to look is at the SQL Agent and to see what jobs are running, what they do, how long they run for and if they could be contributing to performance issues. This is particularly relevant when multiple jobs doing different tasks are run on a number of servers.

So first we need a T-SQL Query that shows us the job history and the job duration. This is found in the msdb database in the sysjobhistory table and the sysjobs table. I could use the sp_help_jobhistory system stored procedure to return most, but not all the information that I need. The query I've written returns exactly the information that I need to get the required view in Excel. Prior to the actual query I need 2 user defined functions, these are to format the run_duration and run_date columns in the sysjobhistory tables into a DATETIME Value.

Herewith the two ufn's:

USE MSDB
GO

CREATE function ufn_Run_time (@Run_Date INT, @Run_time INT)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @TL INT
DECLARE @T VARCHAR(30)
DECLARE @RTC VARCHAR(10)
DECLARE @DS VARCHAR(15)

SET @TL = LEN(@Run_time)
SET @RTC = CAST(@Run_time AS VARCHAR(10))

SET @DS = CAST(@Run_Date AS VARCHAR(15))
SET @DS = SUBSTRING(@DS, 1, 4)+'/'+SUBSTRING(@DS, 5, 2)+'/'+SUBSTRING(@DS, 7, 2)

IF (@TL = 5)
 BEGIN
 SET @T = '0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)+':'+SUBSTRING(@RTC,4,2)
 END
ELSE
 IF (@TL = 1)
 BEGIN
 SET @T = '00:00:0'+@RTC
 END
ELSE
 IF (@TL = 2)
 BEGIN
 SET @T = '00:00:'+@RTC
 END
ELSE
 IF (@TL = 3)
 BEGIN
 SET @T = '00:0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)
 END
ELSE
 IF (@TL = 4)
 BEGIN
 SET @T = '00:'+SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)
 END
ELSE
 SET @T = SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)+':'+SUBSTRING(@RTC,5,2)

SET @T = @DS + ' ' + @T
RETURN(@T)
END


--select (dbo.ufn_Run_time(20090815, 1234))

USE MSDB
GO

CREATE function ufn_Run_Duration (@Run_time INT)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @TL INT
DECLARE @T VARCHAR(30)
DECLARE @RTC VARCHAR(10)
DECLARE @DS VARCHAR(30)
SET @TL = LEN(@Run_time)

SET @RTC = CAST(@Run_time AS VARCHAR(10))


SET @DS = '19000101'
SET @DS = SUBSTRING(@DS, 1, 4)+'/'+SUBSTRING(@DS, 5, 2)+'/'+SUBSTRING(@DS, 7, 2)

IF (@TL = 5)
 BEGIN
 SET @T = '0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)+':'+SUBSTRING(@RTC,4,2)
 END
ELSE
 IF (@TL = 1)
 BEGIN
 SET @T = '00:00:0'+@RTC
 END
ELSE
 IF (@TL = 2)
 BEGIN
 SET @T = '00:00:'+@RTC
 END
ELSE
 IF (@TL = 3)
 BEGIN
 SET @T = '00:0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)
 END
ELSE
 IF (@TL = 4)
 BEGIN
 SET @T = '00:'+SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)
 END
ELSE
 SET @T = SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)+':'+SUBSTRING(@RTC,5,2)

--SET @T = @DS + ' ' + @T
RETURN(@T)
END


--select dbo.ufn_Run_Duration(1234)

Right, so now for the T-SQL query:

select jh.Server + ' - ' + j.Name AS ServerJob,
CONVERT(VARCHAR(30),CAST(dbo.ufn_Run_time(Run_Date, Run_Time) AS DATETIME), 120) AS StartTime,
CONVERT(VARCHAR(30), CAST(dbo.ufn_Run_time(Run_Date, Run_Time) AS DATETIME) + CAST(dbo.ufn_Run_Duration(Run_Duration) AS DATETIME), 120) AS EndTime
from sysjobhistory jh inner join sysjobs j
on jh.job_id = j.job_id
where run_date = '20100308' AND Name NOT LIKE '%Biz%' AND Name NOT LIKE 'ASP%' AND Name NOT LIKE '%Mail%' 
AND Name NOT LIKE 'Update DbIo' AND Name NOT LIKE '%distribution' AND Name NOT LIKE '%Capture DB%' 
AND Name NOT LIKE '%Replication%' AND Name NOT LIKE '%Mobile - Process%' AND NAME NOT LIKE 'MSCRM_CONFIG.HardDelete'
and step_name = '(Job outcome)'
--AND Run_Duration > 30

This shows me the Server and Job name and the StartTime and EndTime of the Job. This is what I require in Excel to show floating bars, across a time scale. And in SQL 2008 Reporting Services, which has Dundas chart controls built in, you can do it to! :-). So the above query is the Dataset query.

I then create a range chart with the floating bars:




The Series data is configured as the two date fields, see below image:



I drop the Server - Job field in as the category field:



If everything is set up correctly, when you preview the report you should have a view of something you could never visualize so succinctly before, all your SQL Agent jobs and their durations across a time scale. Son, its a beautiful thing.... :-).




Now for the final bit, I'm gonna write some pseudo code. ;-). Basically I want to be able to click on a Job bar and be able to change its start time. So I'll need a second report that does the work of changing the schedule. A report that changes code?! Yip, it is possible. :-). This 2nd report takes the job name as an input parameter. On the 1st report I set a click action to navigate to the second report and populates the job name parameter. The only other input needed is the new start time, in the format of hhmmss, i.e. 031500 for a start time of 03:15am. I'll post the stored procedure code below to actually affect the schedule change. The report bit you'll have to figure out. If you don't come right, I can email you the rdl. ;-).

Code to change job schedule from Reporting Services:

ALTER PROC usp_Change_JobStartTime @JobName VARCHAR(255), @NewTime VARCHAR(50)
AS
BEGIN
DECLARE @ScheduleID INT
DECLARE @ReturnString VARCHAR(500)
DECLARE @ReturnTime VARCHAR(50)
--SET @JobName = 'Toets32'
--SET @NewTime = '122920'

SET @ScheduleID = (SELECT s.schedule_id FROM sysjobschedules s INNER JOIN sysjobs j
on s.job_id = j.job_id WHERE  j.Name = @JobName)

exec sp_update_schedule @schedule_id = @ScheduleID, @active_start_time = @NewTime

SET @ReturnTime = (select active_start_time from sysschedules WHERE schedule_id = @ScheduleID)
SET @ReturnString = 'Job: ' + @JobName + ' Start time has been changed to ' + (dbo.ufn_Run_Duration(@ReturnTime))
SELECT @ReturnString 
END

usp_Change_JobStartTime 'Toets32', '124935'