Skip to content

Instantly share code, notes, and snippets.

@TheRockStarDBA

TheRockStarDBA/SQLAgent_Duration_Schedule.sql

Created October 15, 2015 16:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save TheRockStarDBA/20d2815a5dfc52e483bf to your computer and use it in GitHub Desktop.
Save TheRockStarDBA/20d2815a5dfc52e483bf to your computer and use it in GitHub Desktop.
Download ZIP
Find the Last_Run_Duration with Avg, Max and Min for SQL Agent RUN Times along with schedules
Raw
SQLAgent_Duration_Schedule.sql
SET NOCOUNT ON
GO
/***********************************************************************************************************
************************************************************************************************************
Author : KIN SHAH - Sr.DBA (MS SQL Server and SybaseASE)
Date : Feb 18th 2014
Purpose : Find the Last_Run_Duration with Avg, Max and Min for SQL Agent RUN Times.
This script can be used to do a trend analysis using SSRS.
RDBMS : Compatible with SQL Server 2005, 2008 R2, 2012, 2014 and 2016 :-)
Compatible with servers running standard and binary Collations as well.
Version : 1.0.0
Change History : ** NONE **
License : This script is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of this script in whole or in part, is prohibited without the author's express
written consent.
************************************************************************************************************
************************************************************************************************************/
-- DROP all temp tables if existed
IF OBJECT_ID('tempdb..#temp_runhistory') IS NOT NULL
DROP TABLE #temp_runhistory;
IF OBJECT_ID('tempdb..#temp_jobhistory') IS NOT NULL
DROP TABLE #temp_jobhistory;
IF OBJECT_ID('tempdb..#schedules') IS NOT NULL
DROP TABLE #schedules;
IF OBJECT_ID('tempdb..#finalReport') IS NOT NULL
DROP TABLE #finalReport;
-- Variable Declaration
DECLARE @num_days INT
DECLARE @first_day DATETIME
DECLARE @last_day DATETIME
DECLARE @first_num INT
IF @num_days IS NULL
SET @num_days = 30 -- this will be how much the job history will be utilize to analyze the average run times !!
SET @last_day = getdate()
SET @first_day = dateadd(dd, - @num_days, @last_day)
SELECT @first_num = cast(year(@first_day) AS CHAR(4)) + replicate('0', 2 - len(month(@first_day))) + cast(month(@first_day) AS VARCHAR(2)) + replicate('0', 2 - len(day(@first_day))) + cast(day(@first_day) AS VARCHAR(2))
SELECT h.instance_id
,h.job_id
,j.name
,h.step_id
,h.step_name
,h.sql_message_id
,h.sql_severity
,h.run_status
,'run_date' = cast(h.run_date AS VARCHAR(8))
,'run_time' = replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6))
,'run_datetime' = left(cast(h.run_date AS VARCHAR(8)), 4) + '/' + substring(cast(h.run_date AS VARCHAR(8)), 5, 2) + '/' + right(cast(h.run_date AS VARCHAR(8)), 2) + ' ' + left(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 2) + ':' + substring(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 3, 2) + ':' + right(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 2)
,run_duration = cast(h.run_duration AS VARCHAR(20))
,run_duration_conv = CASE
WHEN (len(cast(h.run_duration AS VARCHAR(20))) < 3)
THEN cast(h.run_duration AS VARCHAR(6))
WHEN (len(cast(h.run_duration AS VARCHAR(20))) = 3)
THEN LEFT(cast(h.run_duration AS VARCHAR(6)), 1) * 60 --min
+ RIGHT(cast(h.run_duration AS VARCHAR(6)), 2) --sec
WHEN (len(cast(h.run_duration AS VARCHAR(20))) = 4)
THEN LEFT(cast(h.run_duration AS VARCHAR(6)), 2) * 60 --min
+ RIGHT(cast(h.run_duration AS VARCHAR(6)), 2) --sec
WHEN (len(cast(h.run_duration AS VARCHAR(20))) >= 5)
THEN (Left(cast(h.run_duration AS VARCHAR(20)), len(h.run_duration) - 4)) * 3600 --hour
+ (substring(cast(h.run_duration AS VARCHAR(20)), len(h.run_duration) - 3, 2)) * 60 --min
+ Right(cast(h.run_duration AS VARCHAR(20)), 2) --sec
END
,h.retries_attempted
,h.server
INTO #temp_jobhistory
FROM msdb..sysjobhistory h
,msdb..sysjobs j
WHERE h.job_id = j.job_id
AND h.run_date >= @first_num
AND h.step_id = 0
option (recompile)
SELECT j.job_id
,j.name
,'Sampling' = (
SELECT count(*)
FROM #temp_jobhistory h
WHERE h.job_id = j.job_id
)
,'fromRunDate' = (
SELECT min(run_date)
FROM #temp_jobhistory h
WHERE h.job_id = j.job_id
)
,'run_duration_max' = (
SELECT max(run_duration_conv)
FROM #temp_jobhistory h
WHERE h.job_id = j.job_id
)
,'run_duration_min' = (
SELECT min(run_duration_conv)
FROM #temp_jobhistory h
WHERE h.job_id = j.job_id
)
,'run_duration_avg' = (
SELECT avg(run_duration_conv)
FROM #temp_jobhistory h
WHERE h.job_id = j.job_id
)
,'Last_RunDate' = (
SELECT max(run_datetime)
FROM #temp_jobhistory h
WHERE h.job_id = j.job_id
)
,'Last_RunStatus' = NULL --(select run_status from #temp_jobhistory h where h.job_id=j.job_id)
,'Last_RunDuration' = NULL
INTO #temp_runhistory
FROM msdb..sysjobs j
WHERE j.enabled = 1 -- only look for enabled JOBS !!
option (recompile)
UPDATE #temp_runhistory
SET Last_RunStatus = j.run_status
,Last_RunDuration = j.run_duration_conv
FROM #temp_jobhistory j
WHERE #temp_runhistory.job_id = j.job_id
AND #temp_runhistory.Last_RunDate = j.run_datetime
AND j.run_datetime = (
SELECT max(run_datetime)
FROM #temp_jobhistory j1
WHERE j1.job_id = #temp_runhistory.job_id
)
---- GET schedules in HUMAN READABLE FORMAT
SELECT SJ.job_id
,SJ.name AS job_name
,CASE freq_type
WHEN 1
THEN 'Occurs on ' + STUFF(RIGHT(active_start_date, 4), 3, 0, '/') + '/' + LEFT(active_start_date, 4) + ' at ' + REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME) /* hh:mm:ss 24H */, 9), 14), ':000', ' ') /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4
THEN 'Occurs every ' + CAST(freq_interval AS VARCHAR(10)) + ' day(s) ' + CASE freq_subday_type
WHEN 1
THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
WHEN 2
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)'
WHEN 4
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)'
WHEN 8
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)'
ELSE ''
END + CASE
WHEN freq_subday_type IN (
2
,4
,8
) /* repeat seconds/mins/hours */
THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 8
THEN 'Occurs every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' week(s) on ' + REPLACE(CASE
WHEN freq_interval & 1 = 1
THEN 'Sunday, '
ELSE ''
END + CASE
WHEN freq_interval & 2 = 2
THEN 'Monday, '
ELSE ''
END + CASE
WHEN freq_interval & 4 = 4
THEN 'Tuesday, '
ELSE ''
END + CASE
WHEN freq_interval & 8 = 8
THEN 'Wednesday, '
ELSE ''
END + CASE
WHEN freq_interval & 16 = 16
THEN 'Thursday, '
ELSE ''
END + CASE
WHEN freq_interval & 32 = 32
THEN 'Friday, '
ELSE ''
END + CASE
WHEN freq_interval & 64 = 64
THEN 'Saturday, '
ELSE ''
END + '|', ', |', ' ') /* get rid of trailing comma */
+ CASE freq_subday_type
WHEN 1
THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
WHEN 2
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)'
WHEN 4
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)'
WHEN 8
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)'
ELSE ''
END + CASE
WHEN freq_subday_type IN (
2
,4
,8
) /* repeat seconds/mins/hours */
THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 16
THEN 'Occurs every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' month(s) on ' + 'day ' + CAST(freq_interval AS VARCHAR(10)) + ' of that month ' + CASE freq_subday_type
WHEN 1
THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
WHEN 2
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)'
WHEN 4
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)'
WHEN 8
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)'
ELSE ''
END + CASE
WHEN freq_subday_type IN (
2
,4
,8
) /* repeat seconds/mins/hours */
THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 32
THEN 'Occurs ' + CASE freq_relative_interval
WHEN 1
THEN 'every first '
WHEN 2
THEN 'every second '
WHEN 4
THEN 'every third '
WHEN 8
THEN 'every fourth '
WHEN 16
THEN 'on the last '
END + CASE freq_interval
WHEN 1
THEN 'Sunday'
WHEN 2
THEN 'Monday'
WHEN 3
THEN 'Tuesday'
WHEN 4
THEN 'Wednesday'
WHEN 5
THEN 'Thursday'
WHEN 6
THEN 'Friday'
WHEN 7
THEN 'Saturday'
WHEN 8
THEN 'day'
WHEN 9
THEN 'weekday'
WHEN 10
THEN 'weekend'
END + ' of every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' month(s) ' + CASE freq_subday_type
WHEN 1
THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
WHEN 2
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)'
WHEN 4
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)'
WHEN 8
THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)'
ELSE ''
END + CASE
WHEN freq_subday_type IN (
2
,4
,8
) /* repeat seconds/mins/hours */
THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 64
THEN 'Runs when the SQL Server Agent service starts'
WHEN 128
THEN 'Runs when the computer is idle'
END AS [Schedule_Description]
INTO #schedules
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobschedules SJS ON SJ.job_id = SJS.job_id
INNER JOIN msdb.dbo.sysschedules SS ON SJS.schedule_id = SS.schedule_id
option (recompile);
--- Get all details into a temp table with all fancy calculations !!
SELECT DISTINCT CONVERT(NVARCHAR(128), SERVERPROPERTY('servername')) AS [Server_Name]
,trh.job_id AS 'Job_ID'
,trh.name AS 'Job_Name'
,trh.Last_RunDate AS 'Last_Run_Date'
,'Last_RunStatus' = CASE trh.Last_RunStatus
WHEN 0
THEN 'Failed'
WHEN 1
THEN 'Succeeded'
WHEN 2
THEN 'Retry'
WHEN 3
THEN 'Canceled'
WHEN 4
THEN 'In progress'
END
-- MIND BOGGLING CALCULATIONS .... IT will spin your head if you try to decode it !!
,'Last_RunDuration (hh:mm:ss)' = cast(trh.Last_RunDuration / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.Last_RunDuration % 3600) / 60)) + cast((trh.Last_RunDuration % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.Last_RunDuration % 3600) % 60)) + cast((trh.Last_RunDuration % 3600) % 60 AS VARCHAR(2))
,'Avg_Duration (hh:mm:ss)' = cast(trh.run_duration_avg / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_avg % 3600) / 60)) + cast((trh.run_duration_avg % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_avg % 3600) % 60)) + cast((trh.run_duration_avg % 3600) % 60 AS VARCHAR(2))
,'Max_Duration (hh:mm:ss)' = cast(trh.run_duration_max / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_max % 3600) / 60)) + cast((trh.run_duration_max % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_max % 3600) % 60)) + cast((trh.run_duration_max % 3600) % 60 AS VARCHAR(2))
,'Min_Duration (hh:mm:ss)' = cast(trh.run_duration_min / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_min % 3600) / 60)) + cast((trh.run_duration_min % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_min % 3600) % 60)) + cast((trh.run_duration_min % 3600) % 60 AS VARCHAR(2))
,trh.fromRunDate AS 'From_Date'
,trh.Sampling
,sched.[Schedule_Description]
INTO #finalReport
FROM #temp_runhistory trh
LEFT JOIN #schedules AS sched ON trh.job_id = sched.job_id
option (recompile)
-- insert back into reporting server (if you have one ...)
--- Generate an AWESOME FINAL REPORT !!
SELECT getdate() AS [Data_Collection_Date]
,[Server_Name]
,[Job_ID]
,[Job_Name]
,[Last_Run_Date]
,[Last_RunStatus]
,[Last_RunDuration (hh:mm:ss)]
,[Avg_Duration (hh:mm:ss)]
,[Max_Duration (hh:mm:ss)]
,[Min_Duration (hh:mm:ss)]
,[From_Date]
,[Sampling]
-- we want to get all the schedule for a single JOB with ;** seperated
,STUFF((
SELECT ';** ' + [Schedule_Description]
FROM #finalReport b
WHERE b.[Job_Name] = a.[Job_Name]
FOR XML PATH('')
), 1, 3, '') AS [Schedule_Description]
FROM #finalReport a
GROUP BY [Server_Name]
,[Job_ID]
,[Job_Name]
,[Last_Run_Date]
,[Last_RunStatus]
,[Last_RunDuration (hh:mm:ss)]
,[Avg_Duration (hh:mm:ss)]
,[Max_Duration (hh:mm:ss)]
,[Min_Duration (hh:mm:ss)]
,[From_Date]
,[Sampling]
ORDER BY [Last_RunDuration (hh:mm:ss)] DESC
option (recompile)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment