Technical Article

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

Rate

2 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

2 (3)

You rated this post out of 5. Change rating