SQL Agent Jobs Summary
This script uses a function ([udf_schedule_description]). You will need to create the function first. CREATE FUNCTION script and more information about this function can be found here: http://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/
Please make sure to run this script in a test environment first to make sure it works as expected.
USE msdb
GO
--Please make sure you've created udf_schedule_description before running the below script.
--http://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/
--Collect Job history information with the help of a CTE
WITH JobHistoryInfo(avg_run_duration_Minutes, job_id)
AS ( SELECT AVG(SUM_run_duration_Minutes) AS avg_run_duration_Minutes, job_id
FROM( SELECT SUM((CONVERT(int,SUBSTRING(run_duration_char, 1, 2))*60)
+ CONVERT(int,SUBSTRING(run_duration_char, 3, 2))) AS SUM_run_duration_Minutes, job_id
FROM ( SELECT job_id, RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6) AS run_duration_char, run_date
FROM sysjobhistory h
WHERE step_id = 0) sjh
GROUP BY job_id, run_date) JobStepsDuration
GROUP BY job_id)
--Review SQL Agent jobs information for an instance of SQL Server 2005
--Where the job and job schedule are both enabled
SELECT dbo.sysjobs.name AS JobName,
CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS ActiveStartTime,
dbo.udf_schedule_description(dbo.sysschedules.freq_type, dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type, dbo.sysschedules.freq_subday_interval, dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor, dbo.sysschedules.active_start_date, dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time, dbo.sysschedules.active_end_time) AS ScheduleDscr,
avg_run_duration_Minutes AS AvgRunDuration_Minutes,
CONVERT(datetime, LEFT(next_run_date, 4) + '/'
+ SUBSTRING(CONVERT(char(8),next_run_date),5,2) + '/' + RIGHT(next_run_date,2)) AS NextRunDate
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id INNER JOIN
JobHistoryInfo h ON h.job_id = sysjobs.job_id
WHERE dbo.sysjobs.enabled = 1
AND sysschedules.enabled = 1
ORDER BY ActiveStartTime, ScheduleDscr, JobName