List All Jobs and Their Schedules

  • Comments posted to this topic are about the item List All Jobs and Their Schedules

  • Do you have the same for SQL2005?

  • Same SQL statement Mike wrote but adapted to SQL Server 2005 and also displaying subday frequencies for those jobs that run multiples times a day.

    /*******************************************************************************

    Name: GetJobSchedule (For SQL Server7.0&2000)

    Author: M.Pearson

    Creation Date: 5 Jun 2002

    Version: 1.0

    Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to

    produce a resultset showing the jobs on a server plus their schedules

    (if applicable) and the maximun duration of the job.

    The UNION join is to cater for jobs that have been scheduled but not yet

    run, as this information is stored in the 'active_start...' fields of the

    sysjobschedules table, whereas if the job has already run the schedule

    information is stored in the 'next_run...' fields of the sysjobschedules table.

    Modification History:

    -------------------------------------------------------------------------------

    Version Date Name Modification

    -------------------------------------------------------------------------------

    1.0 5 Jun 2002 M.Pearson Inital Creation

    1.1 6 May 2009 A. Gonzalez Adapted to SQL Server 2005 and to show

    subday frequencies.

    *******************************************************************************/

    USE msdb

    Go

    SELECT dbo.sysjobs.Name AS 'Job Name',

    'Job Enabled' = CASE dbo.sysjobs.Enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END,

    'Frequency' = CASE dbo.sysschedules.freq_type

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly relative'

    WHEN 64 THEN 'When SQLServer Agent starts'

    END,

    'Start Date' = CASE active_start_date

    WHEN 0 THEN null

    ELSE

    substring(convert(varchar(15),active_start_date),1,4) + '/' +

    substring(convert(varchar(15),active_start_date),5,2) + '/' +

    substring(convert(varchar(15),active_start_date),7,2)

    END,

    'Start Time' = CASE len(active_start_time)

    WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))

    WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))

    WHEN 3 THEN cast('00:0'

    + Left(right(active_start_time,3),1)

    +':' + right(active_start_time,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(active_start_time,5),1)

    +':' + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    WHEN 6 THEN cast(Left(right(active_start_time,6),2)

    +':' + Left(right(active_start_time,4),2)

    +':' + right(active_start_time,2) as char (8))

    END,

    -- active_start_time as 'Start Time',

    CASE len(run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(run_duration,3),1)

    +':' + right(run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(run_duration,5),1)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(run_duration,6),2)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    END as 'Max Duration',

    CASE(dbo.sysschedules.freq_subday_interval)

    WHEN 0 THEN 'Once'

    ELSE cast('Every '

    + right(dbo.sysschedules.freq_subday_interval,2)

    + ' '

    + CASE(dbo.sysschedules.freq_subday_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Minutes'

    WHEN 8 THEN 'Hours'

    END as char(16))

    END as 'Subday Frequency'

    FROM dbo.sysjobs

    LEFT OUTER 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

    LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

    FROM dbo.sysjobhistory

    GROUP BY job_id) Q1

    ON dbo.sysjobs.job_id = Q1.job_id

    WHERE Next_run_time = 0

    UNION

    SELECT dbo.sysjobs.Name AS 'Job Name',

    'Job Enabled' = CASE dbo.sysjobs.Enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END,

    'Frequency' = CASE dbo.sysschedules.freq_type

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly relative'

    WHEN 64 THEN 'When SQLServer Agent starts'

    END,

    'Start Date' = CASE next_run_date

    WHEN 0 THEN null

    ELSE

    substring(convert(varchar(15),next_run_date),1,4) + '/' +

    substring(convert(varchar(15),next_run_date),5,2) + '/' +

    substring(convert(varchar(15),next_run_date),7,2)

    END,

    'Start Time' = CASE len(next_run_time)

    WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))

    WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))

    WHEN 3 THEN cast('00:0'

    + Left(right(next_run_time,3),1)

    +':' + right(next_run_time,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

    +':' + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    WHEN 6 THEN cast(Left(right(next_run_time,6),2)

    +':' + Left(right(next_run_time,4),2)

    +':' + right(next_run_time,2) as char (8))

    END,

    -- next_run_time as 'Start Time',

    CASE len(run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(run_duration,3),1)

    +':' + right(run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(run_duration,5),1)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(run_duration,6),2)

    +':' + Left(right(run_duration,4),2)

    +':' + right(run_duration,2) as char (8))

    END as 'Max Duration',

    CASE(dbo.sysschedules.freq_subday_interval)

    WHEN 0 THEN 'Once'

    ELSE cast('Every '

    + right(dbo.sysschedules.freq_subday_interval,2)

    + ' '

    + CASE(dbo.sysschedules.freq_subday_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Minutes'

    WHEN 8 THEN 'Hours'

    END as char(16))

    END as 'Subday Frequency'

    FROM dbo.sysjobs

    LEFT OUTER 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

    LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

    FROM dbo.sysjobhistory

    GROUP BY job_id) Q1

    ON dbo.sysjobs.job_id = Q1.job_id

    WHERE Next_run_time 0

    ORDER BY [Start Date],[Start Time]

  • @agonzal2

    modified it, see comments in the code

    /*******************************************************************************

    Name: GetJobSchedule (For SQL Server7.0&2000)

    Author: M.Pearson

    Creation Date: 5 Jun 2002

    Version: 1.0

    Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to

    produce a resultset showing the jobs on a server plus their schedules

    (if applicable) and the maximun duration of the jojob.

    The UNION join is to cater for schedules not yet

    run, as this information is stored in the 'active_start...' fields of the

    sysjobschedules table, whereas if the job has already run the schedule

    information is stored in the 'next_run...' fields of the sysjobschedules table.

    Note: when a job has multiple schedules it appears multiple itmes in the list

    Modification History:

    -------------------------------------------------------------------------------

    Version Date Name Modification

    -------------------------------------------------------------------------------

    1.0 5 Jun 2002 M.Pearson Inital Creation

    1.1 6 May 2009 job_schd. Gonzalez Adapted to SQL Server 2005 and to show

    subday frequencies.

    1.2 7 Jul 2016 Jaybrown845 use field Next_run_date instead of Next_run_time to determine lookup to active_start_date field (when using field Next_run_time it does not show next run time for schedules running at 12 AM)

    changed join to a left join to include jobs witout any schedule

    Added fileds: schedule id, scheduled enabled, schedule end date and time, job fail notify mail and email

    combined seperated queries into a single query

    prepared fo view creation

    reformatted

    *******************************************************************************/

    USE msdb

    GO

    CREATE VIEW dbo.vw_job_and_schedules

    AS

    SELECT

    [Job ID] = job.job_id

    ,[Job Name] = job.Name

    ,[Job Enabled] =

    CASE job.Enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END

    ,[Sched ID] = sched.schedule_id

    ,[Sched Enabled] =

    CASE sched.enabled

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    END

    ,[Sched Frequency] =

    CASE sched.freq_type

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly relative'

    WHEN 64 THEN 'When SQLServer Agent starts'

    END

    ,[Next Run Date] =

    CASE next_run_date

    WHEN 0 THEN NULL

    ELSE SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 1, 4) + '/' +

    SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 5, 2) + '/' +

    SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 7, 2)

    END

    ,[Next Run Time] =

    CASE LEN(next_run_time)

    WHEN 1 THEN CAST('00:00:0' + RIGHT(next_run_time, 2) AS CHAR(8))

    WHEN 2 THEN CAST('00:00:' + RIGHT(next_run_time, 2) AS CHAR(8))

    WHEN 3 THEN CAST('00:0'

    + LEFT(RIGHT(next_run_time, 3), 1)

    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))

    WHEN 4 THEN CAST('00:'

    + LEFT(RIGHT(next_run_time, 4), 2)

    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))

    WHEN 5 THEN CAST('0'

    + LEFT(RIGHT(next_run_time, 5), 1)

    + ':' + LEFT(RIGHT(next_run_time, 4), 2)

    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))

    WHEN 6 THEN CAST(LEFT(RIGHT(next_run_time, 6), 2)

    + ':' + LEFT(RIGHT(next_run_time, 4), 2)

    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))

    END

    ,[Max Duration] =

    CASE LEN(run_duration)

    WHEN 1 THEN CAST('00:00:0'

    + CAST(run_duration AS CHAR) AS CHAR(8))

    WHEN 2 THEN CAST('00:00:'

    + CAST(run_duration AS CHAR) AS CHAR(8))

    WHEN 3 THEN CAST('00:0'

    + LEFT(RIGHT(run_duration, 3), 1)

    + ':' + RIGHT(run_duration, 2) AS CHAR(8))

    WHEN 4 THEN CAST('00:'

    + LEFT(RIGHT(run_duration, 4), 2)

    + ':' + RIGHT(run_duration, 2) AS CHAR(8))

    WHEN 5 THEN CAST('0'

    + LEFT(RIGHT(run_duration, 5), 1)

    + ':' + LEFT(RIGHT(run_duration, 4), 2)

    + ':' + RIGHT(run_duration, 2) AS CHAR(8))

    WHEN 6 THEN CAST(LEFT(RIGHT(run_duration, 6), 2)

    + ':' + LEFT(RIGHT(run_duration, 4), 2)

    + ':' + RIGHT(run_duration, 2) AS CHAR(8))

    END

    ,[Subday Frequency] =

    CASE (sched.freq_subday_interval)

    WHEN 0 THEN 'Once'

    ELSE CAST('Every '

    + RIGHT(sched.freq_subday_interval, 2)

    + ' '

    + CASE (sched.freq_subday_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Minutes'

    WHEN 8 THEN 'Hours'

    END AS CHAR(16))

    END

    ,[Sched End Date] = sched.active_end_date

    ,[Sched End Time] = sched.active_end_time

    ,[Fail Notify Name] =

    CASE

    WHEN oper.enabled = 0 THEN 'Disabled: '

    ELSE ''

    END + oper.name

    ,[Fail Notify Email] = oper.email_address

    FROM dbo.sysjobs job

    LEFT JOIN (SELECT

    job_schd.job_id

    ,sys_schd.enabled

    ,sys_schd.schedule_id

    ,sys_schd.freq_type

    ,sys_schd.freq_subday_type

    ,sys_schd.freq_subday_interval

    ,next_run_date =

    CASE

    WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_date

    ELSE job_schd.next_run_date

    END

    ,next_run_time =

    CASE

    WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_time

    ELSE job_schd.next_run_time

    END

    ,active_end_date = NULLIF(sys_schd.active_end_date, '99991231')

    ,active_end_time = NULLIF(sys_schd.active_end_time, '235959')

    FROM dbo.sysjobschedules job_schd

    LEFT JOIN dbo.sysschedules sys_schd

    ON job_schd.schedule_id = sys_schd.schedule_id) sched

    ON job.job_id = sched.job_id

    LEFT OUTER JOIN (SELECT

    job_id

    ,MAX(job_his.run_duration) AS run_duration

    FROM dbo.sysjobhistory job_his

    GROUP BY job_id) Q1

    ON job.job_id = Q1.job_id

    LEFT JOIN sysoperators oper

    ON job.notify_email_operator_id = oper.id

  • Beautiful code

  • The best of all worlds:

    use [msdb]

    Declare @weekDay Table
    (
      mask int
    , maskValue varchar(32)
    );

    Insert Into @weekDay
      Select 1, 'Sunday'  UNION ALL
      Select 2, 'Monday'  UNION ALL
      Select 4, 'Tuesday'  UNION ALL
      Select 8, 'Wednesday' UNION ALL
      Select 16, 'Thursday' UNION ALL
      Select 32, 'Friday'  UNION ALL
      Select 64, 'Saturday';

    With SCHED as (
    Select sched.name As 'scheduleName'
      , sched.schedule_id
      , jobsched.job_id as job_id
      , Case
       When sched.freq_type = 1
        Then 'Once'
       When sched.freq_type = 4 And sched.freq_interval = 1
        Then 'Daily'
       When sched.freq_type = 4
        Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
       When sched.freq_type = 8
        Then Replace( Replace( Replace((
          Select maskValue
          From @weekDay As x
          Where sched.freq_interval & x.mask <> 0
          Order By mask For XML Raw)
       , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '')
       + Case When sched.freq_recurrence_factor <> 0
       And sched.freq_recurrence_factor = 1
        Then '; weekly'
      When sched.freq_recurrence_factor <> 0
        Then '; every '
        + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks'
       End
       When sched.freq_type = 16
        Then 'On day '
        + Cast(sched.freq_interval As varchar(10)) + ' of every '
        + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
       When sched.freq_type = 32
        Then Case
        When sched.freq_relative_interval = 1
          Then 'First'
        When sched.freq_relative_interval = 2
          Then 'Second'
        When sched.freq_relative_interval = 4
          Then 'Third'
        When sched.freq_relative_interval = 8
          Then 'Fourth'
        When sched.freq_relative_interval = 16
          Then 'Last'
      End +
      Case
       When sched.freq_interval = 1
        Then ' Sunday'
       When sched.freq_interval = 2
        Then ' Monday'
       When sched.freq_interval = 3
        Then ' Tuesday'
       When sched.freq_interval = 4
        Then ' Wednesday'
       When sched.freq_interval = 5
        Then ' Thursday'
       When sched.freq_interval = 6
        Then ' Friday'
       When sched.freq_interval = 7
        Then ' Saturday'
       When sched.freq_interval = 8
        Then ' Day'
       When sched.freq_interval = 9
        Then ' Weekday'
       When sched.freq_interval = 10
        Then ' Weekend'
      End
      +
      Case
       When sched.freq_recurrence_factor <> 0
       And sched.freq_recurrence_factor = 1
        Then '; monthly'
       When sched.freq_recurrence_factor <> 0
        Then '; every '
      + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
      End
      When sched.freq_type = 64
       Then 'StartUp'
      When sched.freq_type = 128
       Then 'Idle'
      End As 'frequency'
      , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) +
      Case
       When sched.freq_subday_type = 2
        Then ' seconds'
       When sched.freq_subday_type = 4
        Then ' minutes'
       When sched.freq_subday_type = 8
        Then ' hours'
      End, 'Once') As 'subFrequency'

         ,[Start_time] =  
          CASE LEN(sched.active_start_time)
           WHEN 1 THEN CAST('00:00:0' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
           WHEN 2 THEN CAST('00:00:' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
           WHEN 3 THEN CAST('00:0'
            + LEFT(RIGHT(sched.active_start_time, 3), 1)
            + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
           WHEN 4 THEN CAST('00:'
            + LEFT(RIGHT(sched.active_start_time, 4), 2)
            + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
           WHEN 5 THEN CAST('0'
            + LEFT(RIGHT(sched.active_start_time, 5), 1)
            + ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
            + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
           WHEN 6 THEN CAST(LEFT(RIGHT(sched.active_start_time, 6), 2)
            + ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
            + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
          END
             ,[End_time] =  
          CASE LEN(sched.active_end_time)
           WHEN 1 THEN CAST('00:00:0' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
           WHEN 2 THEN CAST('00:00:' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
           WHEN 3 THEN CAST('00:0'
            + LEFT(RIGHT(sched.active_end_time, 3), 1)
            + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
           WHEN 4 THEN CAST('00:'
            + LEFT(RIGHT(sched.active_end_time, 4), 2)
            + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
           WHEN 5 THEN CAST('0'
            + LEFT(RIGHT(sched.active_end_time, 5), 1)
            + ':' + LEFT(RIGHT(sched.active_end_time, 4), 2)
            + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
           WHEN 6 THEN CAST(LEFT(RIGHT(sched.active_end_time, 6), 2)
            + ':' + LEFT(RIGHT(sched.active_end_time, 4), 2)
            + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
          END
      , Replicate('0', 6 - Len(jobsched.next_run_time))
       + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
      , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
      From msdb.dbo.sysschedules As sched
      Join msdb.dbo.sysjobschedules As jobsched
      On sched.schedule_id = jobsched.schedule_id),
    JOB as (
    SELECT
     [job_id] =    job.job_id
     ,[Job_Name] =   job.Name
     ,[Job_Enabled] =  
         CASE job.Enabled
          WHEN 1 THEN 'Yes'
          WHEN 0 THEN 'No'
         END
     ,[Sched_ID] =   sched.schedule_id
     ,[Sched_Enabled] =  
          CASE sched.enabled
           WHEN 1 THEN 'Yes'
           WHEN 0 THEN 'No'
          END
     ,[Sched_Frequency] = 
          CASE sched.freq_type
           WHEN 1 THEN 'Once'
           WHEN 4 THEN 'Daily'
           WHEN 8 THEN 'Weekly'
           WHEN 16 THEN 'Monthly'
           WHEN 32 THEN 'Monthly relative'
           WHEN 64 THEN 'When SQLServer Agent starts'
          END
     ,[Next_Run_Date] =  
          CASE next_run_date
           WHEN 0 THEN NULL
           ELSE SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 1, 4) + '/' +
            SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 5, 2) + '/' +
            SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 7, 2)
          END
     ,[Next_Run_Time] =  
          CASE LEN(next_run_time)
           WHEN 1 THEN CAST('00:00:0' + RIGHT(next_run_time, 2) AS CHAR(8))
           WHEN 2 THEN CAST('00:00:' + RIGHT(next_run_time, 2) AS CHAR(8))
           WHEN 3 THEN CAST('00:0'
            + LEFT(RIGHT(next_run_time, 3), 1)
            + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
           WHEN 4 THEN CAST('00:'
            + LEFT(RIGHT(next_run_time, 4), 2)
            + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
           WHEN 5 THEN CAST('0'
            + LEFT(RIGHT(next_run_time, 5), 1)
            + ':' + LEFT(RIGHT(next_run_time, 4), 2)
            + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
           WHEN 6 THEN CAST(LEFT(RIGHT(next_run_time, 6), 2)
            + ':' + LEFT(RIGHT(next_run_time, 4), 2)
            + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
          END
     ,[Max_Duration] =  
          CASE LEN(max_run_duration)
           WHEN 1 THEN CAST('00:00:0'
            + CAST(max_run_duration AS CHAR) AS CHAR(8))
           WHEN 2 THEN CAST('00:00:'
            + CAST(max_run_duration AS CHAR) AS CHAR(8))
           WHEN 3 THEN CAST('00:0'
            + LEFT(RIGHT(max_run_duration, 3), 1)
            + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
           WHEN 4 THEN CAST('00:'
            + LEFT(RIGHT(max_run_duration, 4), 2)
            + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
           WHEN 5 THEN CAST('0'
            + LEFT(RIGHT(max_run_duration, 5), 1)
            + ':' + LEFT(RIGHT(max_run_duration, 4), 2)
            + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
           WHEN 6 THEN CAST(LEFT(RIGHT(max_run_duration, 6), 2)
            + ':' + LEFT(RIGHT(max_run_duration, 4), 2)
            + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
          END
     ,[Min_Duration] =  
          CASE LEN(min_run_duration)
           WHEN 1 THEN CAST('00:00:0'
            + CAST(min_run_duration AS CHAR) AS CHAR(8))
           WHEN 2 THEN CAST('00:00:'
            + CAST(min_run_duration AS CHAR) AS CHAR(8))
           WHEN 3 THEN CAST('00:0'
            + LEFT(RIGHT(min_run_duration, 3), 1)
            + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
           WHEN 4 THEN CAST('00:'
            + LEFT(RIGHT(min_run_duration, 4), 2)
            + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
           WHEN 5 THEN CAST('0'
            + LEFT(RIGHT(min_run_duration, 5), 1)
            + ':' + LEFT(RIGHT(min_run_duration, 4), 2)
            + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
           WHEN 6 THEN CAST(LEFT(RIGHT(min_run_duration, 6), 2)
            + ':' + LEFT(RIGHT(min_run_duration, 4), 2)
            + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
          END
     ,[Avg_Duration] =  
          CASE LEN(avg_run_duration)
           WHEN 1 THEN CAST('00:00:0'
            + CAST(avg_run_duration AS CHAR) AS CHAR(8))
           WHEN 2 THEN CAST('00:00:'
            + CAST(avg_run_duration AS CHAR) AS CHAR(8))
           WHEN 3 THEN CAST('00:0'
            + LEFT(RIGHT(avg_run_duration, 3), 1)
            + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
           WHEN 4 THEN CAST('00:'
            + LEFT(RIGHT(avg_run_duration, 4), 2)
            + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
           WHEN 5 THEN CAST('0'
            + LEFT(RIGHT(avg_run_duration, 5), 1)
            + ':' + LEFT(RIGHT(avg_run_duration, 4), 2)
            + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
           WHEN 6 THEN CAST(LEFT(RIGHT(avg_run_duration, 6), 2)
            + ':' + LEFT(RIGHT(avg_run_duration, 4), 2)
            + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
          END
     ,[Subday_Frequency] = 
           CASE (sched.freq_subday_interval)
            WHEN 0 THEN 'Once'
            ELSE CAST('Every '
             + RIGHT(sched.freq_subday_interval, 2)
             + ' '
             + CASE (sched.freq_subday_type)
              WHEN 1 THEN 'Once'
              WHEN 4 THEN 'Minutes'
              WHEN 8 THEN 'Hours'
             END AS CHAR(16))
           END
     ,[Sched_End Date] =  sched.active_end_date
     ,[Sched_End Time] =  sched.active_end_time
     ,[Fail_Notify_Name] = 
           CASE
            WHEN oper.enabled = 0 THEN 'Disabled: '
            ELSE ''
           END + oper.name
     ,[Fail_Notify_Email] = oper.email_address
     ,server

    FROM dbo.sysjobs job
    LEFT JOIN (SELECT

      job_schd.job_id
      ,sys_schd.enabled
      ,sys_schd.schedule_id
      ,sys_schd.freq_type
      ,sys_schd.freq_subday_type
      ,sys_schd.freq_subday_interval
      ,next_run_date = 
          CASE
           WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_date
           ELSE job_schd.next_run_date
          END
      ,next_run_time = 
          CASE
           WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_time
           ELSE job_schd.next_run_time
          END
      ,active_end_date = NULLIF(sys_schd.active_end_date, '99991231')
      ,active_end_time = NULLIF(sys_schd.active_end_time, '235959')

     FROM dbo.sysjobschedules job_schd
     LEFT JOIN dbo.sysschedules sys_schd
      ON job_schd.schedule_id = sys_schd.schedule_id) sched
      ON job.job_id = sched.job_id
    LEFT OUTER JOIN (SELECT
      job_id, server
      ,MAX(job_his.run_duration) AS max_run_duration
      ,MIN(job_his.run_duration) AS min_run_duration
      ,AVG(job_his.run_duration) AS avg_run_duration
     FROM dbo.sysjobhistory job_his
     GROUP BY job_id, server) Q1
     ON job.job_id = Q1.job_id
    LEFT JOIN sysoperators oper
    ON job.notify_email_operator_id = oper.id)

    SELECT isnull(b.server,convert(varchar(max),SERVERPROPERTY('ServerName'))), b.job_name, b.job_enabled, isnull(b.sched_enabled,'No') as sched_enabled,
       isnull(a.scheduleName, 'None') as scheduleName, isnull(a.frequency,'Not scheduled') as frequency,
       isnull(a.subFrequency, 'None') as subFrequency, isnull(a.start_time,'-') as start_time, isnull(a.end_Time,'-') as end_time,
            isnull(b.Next_Run_Date, '-') as Next_Run_Date, isnull(b.Next_Run_Time, '-') as Next_Run_Time,
       isnull(b.Max_Duration, '-') as Max_Duration,  isnull(b.Min_Duration, '-') as Min_Duration,
            isnull(b.Avg_Duration, '-') as Avg_Duration,  isnull(b.Fail_Notify_Name, 'None') as Fail_Notify_Name,
            isnull(b.Fail_Notify_Email, 'None') as Fail_Notify_Email
    FROM SCHED a RIGHT OUTER JOIN JOB b
    ON a.job_id = b.job_id
    ORDER BY job_name

  • rwest 44523 - Wednesday, July 19, 2017 8:04 AM

    The best of all worlds:

    use [msdb]

    Declare @weekDay Table
    (
      mask int
    , maskValue varchar(32)
    );
    ................................................................

    Thank you!  This is exactly what I've been looking for.  Extremely useful script.

  • camaro305hp - Monday, November 20, 2017 3:57 PM

    rwest 44523 - Wednesday, July 19, 2017 8:04 AM

    The best of all worlds:

    use [msdb]

    Declare @weekDay Table
    (
      mask int
    , maskValue varchar(32)
    );
    ................................................................

    Thank you!  This is exactly what I've been looking for.  Extremely useful script.

    Since it doesn't tell you of any failures, cancellations, nor how many times something has executed in, say, the last 24 hours, what will you do with the information that it produces?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Monday, November 20, 2017 4:23 PM

    camaro305hp - Monday, November 20, 2017 3:57 PM

    rwest 44523 - Wednesday, July 19, 2017 8:04 AM

    The best of all worlds:

    use [msdb]

    Declare @weekDay Table
    (
      mask int
    , maskValue varchar(32)
    );
    ................................................................

    Thank you!  This is exactly what I've been looking for.  Extremely useful script.

    Since it doesn't tell you of any failures, cancellations, nor how many times something has executed in, say, the last 24 hours, what will you do with the information that it produces?

    For my purposes of using this script, I'm not interested in those things (I am interested in them, but that's not what I'm using this script for).  I'm using it to do a daily backup of all my jobs/schedules.  In case of catastrophic failure, I can have a quick list of all the jobs/steps/schedules that I can use to quickly get the jobs back up without messing around with the msdb backup.  I do the same thing with logins and linked servers.

  • camaro305hp - Monday, November 20, 2017 5:02 PM

    Jeff Moden - Monday, November 20, 2017 4:23 PM

    camaro305hp - Monday, November 20, 2017 3:57 PM

    rwest 44523 - Wednesday, July 19, 2017 8:04 AM

    The best of all worlds:

    use [msdb]

    Declare @weekDay Table
    (
      mask int
    , maskValue varchar(32)
    );
    ................................................................

    Thank you!  This is exactly what I've been looking for.  Extremely useful script.

    Since it doesn't tell you of any failures, cancellations, nor how many times something has executed in, say, the last 24 hours, what will you do with the information that it produces?

    For my purposes of using this script, I'm not interested in those things (I am interested in them, but that's not what I'm using this script for).  I'm using it to do a daily backup of all my jobs/schedules.  In case of catastrophic failure, I can have a quick list of all the jobs/steps/schedules that I can use to quickly get the jobs back up without messing around with the msdb backup.  I do the same thing with logins and linked servers.

    Got it.  Thanks for the feedback.

    You do know that you can script out all of the jobs, correct? 
    1.  In the Explorer Window of SSMS (press the {f8} key to get there if you don't know how), scroll down to the "SQL Agent Agent", expand it, the single click on JOBS. 
    2. Once that's done, press the {f7} key to open the Explorer Detail window.  There, all of the jobs will be listed. 
    3. Select all of the jobs at once and then right click on the selection.  Select "Script Job As" and then follow your nose.  All the selected jobs will be scripted to the destination you chose and that includes the schedules and everything else.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Monday, November 20, 2017 6:52 PM

    camaro305hp - Monday, November 20, 2017 5:02 PM

    Jeff Moden - Monday, November 20, 2017 4:23 PM

    camaro305hp - Monday, November 20, 2017 3:57 PM

    rwest 44523 - Wednesday, July 19, 2017 8:04 AM

    The best of all worlds:

    use [msdb]

    Declare @weekDay Table
    (
      mask int
    , maskValue varchar(32)
    );
    ................................................................

    Thank you!  This is exactly what I've been looking for.  Extremely useful script.

    Since it doesn't tell you of any failures, cancellations, nor how many times something has executed in, say, the last 24 hours, what will you do with the information that it produces?

    For my purposes of using this script, I'm not interested in those things (I am interested in them, but that's not what I'm using this script for).  I'm using it to do a daily backup of all my jobs/schedules.  In case of catastrophic failure, I can have a quick list of all the jobs/steps/schedules that I can use to quickly get the jobs back up without messing around with the msdb backup.  I do the same thing with logins and linked servers.

    Got it.  Thanks for the feedback.

    You do know that you can script out all of the jobs, correct? 
    1.  In the Explorer Window of SSMS (press the {f8} key to get there if you don't know how), scroll down to the "SQL Agent Agent", expand it, the single click on JOBS. 
    2. Once that's done, press the {f7} key to open the Explorer Detail window.  There, all of the jobs will be listed. 
    3. Select all of the jobs at once and then right click on the selection.  Select "Script Job As" and then follow your nose.  All the selected jobs will be scripted to the destination you chose and that includes the schedules and everything else.

    Thanks for your feedback.  I am aware of the ability to script all jobs from SSMS - I use that method when I'm migrating to new servers and such.  I just wanted something automated that would run daily from a scheduled job.  I have enough people adding jobs and steps without my knowledge that I just didn't want to get caught without up to date information in the worst case scenario.

  • Got it.  Again, thanks for the feedback.  It's always good to know what people do and why.  I appreciate it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello all, I just came across this code and i made some tiny improvements so that it works against all collation settings:


    use [msdb]

    Declare @weekDay Table
    (
    mask int
    , maskValue varchar(32)
    );

    Insert Into @weekDay
    Select 1, 'Sunday' UNION ALL
    Select 2, 'Monday' UNION ALL
    Select 4, 'Tuesday' UNION ALL
    Select 8, 'Wednesday' UNION ALL
    Select 16, 'Thursday' UNION ALL
    Select 32, 'Friday' UNION ALL
    Select 64, 'Saturday';

    With SCHED as (
    Select sched.name As 'scheduleName'
    , sched.schedule_id
    , jobsched.job_id as job_id
    , Case
    When sched.freq_type = 1
    Then 'Once'
    When sched.freq_type = 4 And sched.freq_interval = 1
    Then 'Daily'
    When sched.freq_type = 4
    Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
    When sched.freq_type = 8
    Then Replace( Replace( Replace((
    Select maskValue
    From @weekDay As x
    Where sched.freq_interval & x.mask <> 0
    Order By mask For XML Raw)
    , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '')
    + Case When sched.freq_recurrence_factor <> 0
    And sched.freq_recurrence_factor = 1
    Then '; weekly'
    When sched.freq_recurrence_factor <> 0
    Then '; every '
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks'
    End
    When sched.freq_type = 16
    Then 'On day '
    + Cast(sched.freq_interval As varchar(10)) + ' of every '
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
    When sched.freq_type = 32
    Then Case
    When sched.freq_relative_interval = 1
    Then 'First'
    When sched.freq_relative_interval = 2
    Then 'Second'
    When sched.freq_relative_interval = 4
    Then 'Third'
    When sched.freq_relative_interval = 8
    Then 'Fourth'
    When sched.freq_relative_interval = 16
    Then 'Last'
    End +
    Case
    When sched.freq_interval = 1
    Then ' Sunday'
    When sched.freq_interval = 2
    Then ' Monday'
    When sched.freq_interval = 3
    Then ' Tuesday'
    When sched.freq_interval = 4
    Then ' Wednesday'
    When sched.freq_interval = 5
    Then ' Thursday'
    When sched.freq_interval = 6
    Then ' Friday'
    When sched.freq_interval = 7
    Then ' Saturday'
    When sched.freq_interval = 8
    Then ' Day'
    When sched.freq_interval = 9
    Then ' Weekday'
    When sched.freq_interval = 10
    Then ' Weekend'
    End
    +
    Case
    When sched.freq_recurrence_factor <> 0
    And sched.freq_recurrence_factor = 1
    Then '; monthly'
    When sched.freq_recurrence_factor <> 0
    Then '; every '
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
    End
    When sched.freq_type = 64
    Then 'StartUp'
    When sched.freq_type = 128
    Then 'Idle'
    End As 'frequency'
    , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) +
    Case
    When sched.freq_subday_type = 2
    Then ' seconds'
    When sched.freq_subday_type = 4
    Then ' minutes'
    When sched.freq_subday_type = 8
    Then ' hours'
    End, 'Once') As 'subFrequency'

    ,[start_time] =
    CASE LEN(sched.active_start_time)
    WHEN 1 THEN CAST('00:00:0' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
    WHEN 2 THEN CAST('00:00:' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
    WHEN 3 THEN CAST('00:0'
    + LEFT(RIGHT(sched.active_start_time, 3), 1)
    + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
    WHEN 4 THEN CAST('00:'
    + LEFT(RIGHT(sched.active_start_time, 4), 2)
    + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
    WHEN 5 THEN CAST('0'
    + LEFT(RIGHT(sched.active_start_time, 5), 1)
    + ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
    + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
    WHEN 6 THEN CAST(LEFT(RIGHT(sched.active_start_time, 6), 2)
    + ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
    + ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
    END
    ,[end_time] =
    CASE LEN(sched.active_end_time)
    WHEN 1 THEN CAST('00:00:0' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
    WHEN 2 THEN CAST('00:00:' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
    WHEN 3 THEN CAST('00:0'
    + LEFT(RIGHT(sched.active_end_time, 3), 1)
    + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
    WHEN 4 THEN CAST('00:'
    + LEFT(RIGHT(sched.active_end_time, 4), 2)
    + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
    WHEN 5 THEN CAST('0'
    + LEFT(RIGHT(sched.active_end_time, 5), 1)
    + ':' + LEFT(RIGHT(sched.active_end_time, 4), 2)
    + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
    WHEN 6 THEN CAST(LEFT(RIGHT(sched.active_end_time, 6), 2)
    + ':' + LEFT(RIGHT(sched.active_end_time, 4), 2)
    + ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
    END
    , Replicate('0', 6 - Len(jobsched.next_run_time))
    + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
    , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    Join msdb.dbo.sysjobschedules As jobsched
    On sched.schedule_id = jobsched.schedule_id),
    JOB as (
    SELECT
    [job_id] = job.job_id
    ,[job_name] = job.name
    ,[job_enabled] =
    CASE job.enabled
    WHEN 1 THEN 'Yes'
    WHEN 0 THEN 'No'
    END
    ,[Sched_ID] = sched.schedule_id
    ,[sched_enabled] =
    CASE sched.enabled
    WHEN 1 THEN 'Yes'
    WHEN 0 THEN 'No'
    END
    ,[Sched_Frequency] =
    CASE sched.freq_type
    WHEN 1 THEN 'Once'
    WHEN 4 THEN 'Daily'
    WHEN 8 THEN 'Weekly'
    WHEN 16 THEN 'Monthly'
    WHEN 32 THEN 'Monthly relative'
    WHEN 64 THEN 'When SQLServer Agent starts'
    END
    ,[Start_Date] =
    CASE next_run_date
    WHEN 0 THEN NULL
    ELSE SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 1, 4) + '/' +
    SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 5, 2) + '/' +
    SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 7, 2)
    END
    ,[Next_Run_Time] =
    CASE LEN(next_run_time)
    WHEN 1 THEN CAST('00:00:0' + RIGHT(next_run_time, 2) AS CHAR(8))
    WHEN 2 THEN CAST('00:00:' + RIGHT(next_run_time, 2) AS CHAR(8))
    WHEN 3 THEN CAST('00:0'
    + LEFT(RIGHT(next_run_time, 3), 1)
    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
    WHEN 4 THEN CAST('00:'
    + LEFT(RIGHT(next_run_time, 4), 2)
    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
    WHEN 5 THEN CAST('0'
    + LEFT(RIGHT(next_run_time, 5), 1)
    + ':' + LEFT(RIGHT(next_run_time, 4), 2)
    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
    WHEN 6 THEN CAST(LEFT(RIGHT(next_run_time, 6), 2)
    + ':' + LEFT(RIGHT(next_run_time, 4), 2)
    + ':' + RIGHT(next_run_time, 2) AS CHAR(8))
    END
    ,[Max_Duration] =
    CASE LEN(max_run_duration)
    WHEN 1 THEN CAST('00:00:0'
    + CAST(max_run_duration AS CHAR) AS CHAR(8))
    WHEN 2 THEN CAST('00:00:'
    + CAST(max_run_duration AS CHAR) AS CHAR(8))
    WHEN 3 THEN CAST('00:0'
    + LEFT(RIGHT(max_run_duration, 3), 1)
    + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
    WHEN 4 THEN CAST('00:'
    + LEFT(RIGHT(max_run_duration, 4), 2)
    + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
    WHEN 5 THEN CAST('0'
    + LEFT(RIGHT(max_run_duration, 5), 1)
    + ':' + LEFT(RIGHT(max_run_duration, 4), 2)
    + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
    WHEN 6 THEN CAST(LEFT(RIGHT(max_run_duration, 6), 2)
    + ':' + LEFT(RIGHT(max_run_duration, 4), 2)
    + ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
    END
    ,[Min_Duration] =
    CASE LEN(min_run_duration)
    WHEN 1 THEN CAST('00:00:0'
    + CAST(min_run_duration AS CHAR) AS CHAR(8))
    WHEN 2 THEN CAST('00:00:'
    + CAST(min_run_duration AS CHAR) AS CHAR(8))
    WHEN 3 THEN CAST('00:0'
    + LEFT(RIGHT(min_run_duration, 3), 1)
    + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
    WHEN 4 THEN CAST('00:'
    + LEFT(RIGHT(min_run_duration, 4), 2)
    + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
    WHEN 5 THEN CAST('0'
    + LEFT(RIGHT(min_run_duration, 5), 1)
    + ':' + LEFT(RIGHT(min_run_duration, 4), 2)
    + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
    WHEN 6 THEN CAST(LEFT(RIGHT(min_run_duration, 6), 2)
    + ':' + LEFT(RIGHT(min_run_duration, 4), 2)
    + ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
    END
    ,[Avg_Duration] =
    CASE LEN(avg_run_duration)
    WHEN 1 THEN CAST('00:00:0'
    + CAST(avg_run_duration AS CHAR) AS CHAR(8))
    WHEN 2 THEN CAST('00:00:'
    + CAST(avg_run_duration AS CHAR) AS CHAR(8))
    WHEN 3 THEN CAST('00:0'
    + LEFT(RIGHT(avg_run_duration, 3), 1)
    + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
    WHEN 4 THEN CAST('00:'
    + LEFT(RIGHT(avg_run_duration, 4), 2)
    + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
    WHEN 5 THEN CAST('0'
    + LEFT(RIGHT(avg_run_duration, 5), 1)
    + ':' + LEFT(RIGHT(avg_run_duration, 4), 2)
    + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
    WHEN 6 THEN CAST(LEFT(RIGHT(avg_run_duration, 6), 2)
    + ':' + LEFT(RIGHT(avg_run_duration, 4), 2)
    + ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
    END
    ,[Subday_Frequency] =
    CASE (sched.freq_subday_interval)
    WHEN 0 THEN 'Once'
    ELSE CAST('Every '
    + RIGHT(sched.freq_subday_interval, 2)
    + ' '
    + CASE (sched.freq_subday_type)
    WHEN 1 THEN 'Once'
    WHEN 4 THEN 'Minutes'
    WHEN 8 THEN 'Hours'
    END AS CHAR(16))
    END
    ,[Sched_End Date] = sched.active_end_date
    ,[Sched_End Time] = sched.active_end_time
    ,[Fail_Notify_Name] =
    CASE
    WHEN oper.enabled = 0 THEN 'Disabled: '
    ELSE ''
    END + oper.name
    ,[Fail_Notify_Email] = oper.email_address
    ,server

    FROM dbo.sysjobs job
    LEFT JOIN (SELECT

    job_schd.job_id
    ,sys_schd.enabled
    ,sys_schd.schedule_id
    ,sys_schd.freq_type
    ,sys_schd.freq_subday_type
    ,sys_schd.freq_subday_interval
    ,next_run_date =
    CASE
    WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_date
    ELSE job_schd.next_run_date
    END
    ,next_run_time =
    CASE
    WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_time
    ELSE job_schd.next_run_time
    END
    ,active_end_date = NULLIF(sys_schd.active_end_date, '99991231')
    ,active_end_time = NULLIF(sys_schd.active_end_time, '235959')

    FROM dbo.sysjobschedules job_schd
    LEFT JOIN dbo.sysschedules sys_schd
    ON job_schd.schedule_id = sys_schd.schedule_id) sched
    ON job.job_id = sched.job_id
    LEFT OUTER JOIN (SELECT
    job_id, server
    ,MAX(job_his.run_duration) AS max_run_duration
    ,MIN(job_his.run_duration) AS min_run_duration
    ,AVG(job_his.run_duration) AS avg_run_duration
    FROM dbo.sysjobhistory job_his
    GROUP BY job_id, server) Q1
    ON job.job_id = Q1.job_id
    LEFT JOIN sysoperators oper
    ON job.notify_email_operator_id = oper.id)

    SELECT isnull(b.server,convert(varchar(max),SERVERPROPERTY('ServerName'))), b.job_name, b.job_enabled, isnull(b.sched_enabled,'No') as sched_enabled,
    isnull(a.scheduleName, 'None') as scheduleName, isnull(a.frequency,'Not scheduled') as frequency,
    isnull(a.subFrequency, 'None') as subFrequency, isnull(a.start_time,'-') as start_time, isnull(a.end_time,'-') as end_time,
    isnull(b.Start_Date, '-') as Start_Date, isnull(b.Next_Run_Time, '-') as Startdate,
    isnull(b.Max_Duration, '-') as Max_Duration, isnull(b.Min_Duration, '-') as Min_Duration,
    isnull(b.Avg_Duration, '-') as Avg_Duration, isnull(b.Fail_Notify_Name, 'None') as Fail_Notify_Name,
    isnull(b.Fail_Notify_Email, 'None') as Fail_Notify_Email
    FROM SCHED a RIGHT OUTER JOIN JOB b
    ON a.job_id = b.job_id
    --WHERE b.job_name = 'YourJobNameGoesHere'
    ORDER BY job_name

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply