List Jobs, Schedules, and Next Scheduled Run Datetimes

by Kennah   Last Updated June 20, 2018 08:06 AM

List Jobs, Schedules, and Next Scheduled Run Datetimes

I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes. However, I don't appear to be succeeding.

sysjobactivity has next_scheduled_run_date, which is what I need, but I cannot figure out to connect it to both the Job and Schedule.

If there's a way other than through sysjobactivity, please guide me there.

SELECT
sj.name AS jobName
, ss.name AS scheduleName
, sja.next_scheduled_run_date
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = sj.job_id
INNER JOIN msdb.dbo.sysjobschedules sjs ON sjs.job_id = sja.job_id
INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
WHERE (sj.name LIKE 'xray%')
AND (sja.next_scheduled_run_date > GETDATE())


Answers 4


I have this SP that will help you. You can pass the JobName as a filter. In case you don't pass any value, it will return the details of all jobs.

CREATE PROCEDURE sp_job_list @jobname VARCHAR(100)=NULL
as

DECLARE @sql VARCHAR(8000)
DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
BEGIN
    DROP TABLE #xp_results
END


CREATE TABLE #xp_results (
     job_id                UNIQUEIDENTIFIER NOT NULL,
     last_run_date         INT              NOT NULL,
     last_run_time         INT              NOT NULL,
     next_run_date         INT              NOT NULL,
     next_run_time         INT              NOT NULL,
     next_run_schedule_id  INT              NOT NULL,
     requested_to_run      INT              NOT NULL, 
     request_source        INT              NOT NULL,
     request_source_id     sysname          COLLATE database_default NULL,
     running               INT              NOT NULL, 
     current_step          INT              NOT NULL,
     current_retry_attempt INT              NOT NULL,
     job_state             INT              NOT NULL)



SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

SELECT 
  j.Name AS JobName
, c.Name AS Category
, CASE j.enabled WHEN 1 THEN 'Yes' else 'No' END as Enabled
, CASE s.enabled WHEN 1 THEN 'Yes' else 'No' END as Scheduled
, j.Description 
, CASE s.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 SQL Server Agent starts' 
     WHEN 128 THEN 'Start whenever the CPU(s) become idle' END as Occurs    
, CASE s.freq_type 
     WHEN  1 THEN 'O'
     WHEN  4 THEN 'Every ' 
        + convert(varchar,s.freq_interval) 
        + ' day(s)'
     WHEN  8 THEN 'Every ' 
        + convert(varchar,s.freq_recurrence_factor) 
        + ' weeks(s) on ' 
        + master.dbo.fn_freq_interval_desc(s.freq_interval)                   
     WHEN 16 THEN 'Day ' + convert(varchar,s.freq_interval) 
        + ' of every ' 
        + convert(varchar,s.freq_recurrence_factor) 
        + ' month(s)' 
     WHEN 32 THEN 'The ' 
        + CASE s.freq_relative_interval 
            WHEN  1 THEN 'First'
            WHEN  2 THEN 'Second'
            WHEN  4 THEN 'Third'    
            WHEN  8 THEN 'Fourth'
            WHEN 16 THEN 'Last' END 
        + CASE s.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 Day' END 
        + ' of every ' 
        + convert(varchar,s.freq_recurrence_factor) 
        + ' month(s)' END AS Occurs_detail  
, CASE s.freq_subday_type 
     WHEN 1 THEN 'Occurs once at ' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
     WHEN 2 THEN 'Occurs every ' 
        + convert(varchar,s.freq_subday_interval) 
        + ' Seconds(s) Starting at ' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + ' ending at ' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 4 THEN 'Occurs every ' 
        + convert(varchar,s.freq_subday_interval) 
        + ' Minute(s) Starting at ' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + ' ending at ' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 8 THEN 'Occurs every ' 
        + convert(varchar,s.freq_subday_interval) 
        + ' Hour(s) Starting at ' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + ' ending at ' 
        + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency
, CASE WHEN s.freq_type =  1 THEN 'On date: ' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
+ ' At time: ' 
          + master.dbo.fn_Time2Str(s.active_start_time)
       WHEN s.freq_type < 64 THEN 'Start date: ' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + ' end date: ' 
          + master.dbo.fn_Date2Str(s.active_end_date) END as Duration
, master.dbo.fn_Date2Str(xp.next_run_date) + ' ' 
    + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date
FROM  msdb.dbo.sysjobs j (NOLOCK)
INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id
INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
INNER JOIN #xp_results xp (NOLOCK) ON j.job_id = xp.job_id
WHERE ( j.Name LIKE [email protected] +'%' OR @jobname IS NULL)
ORDER BY j.name
Dan
Dan
August 30, 2016 14:37 PM

I have this script in my toolbox:

    select 
       S.name AS JobName,
       SS.name AS ScheduleName,                    
       CASE(SS.freq_type)
            WHEN 1  THEN 'Once'
            WHEN 4  THEN 'Daily'
            WHEN 8  THEN (case when (SS.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
            WHEN 16 THEN (case when (SS.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' else 'Monthly' end)
            WHEN 32 THEN 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' -- RELATIVE
            WHEN 64 THEN 'SQL Startup'
            WHEN 128 THEN 'SQL Idle'
            ELSE '??'
        END AS Frequency,  
       CASE
            WHEN (freq_type = 1)                       then 'One time only'
            WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
            WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
            WHEN (freq_type = 8) then (select 'Weekly Schedule' = MIN(D1+ D2+D3+D4+D5+D6+D7 )
                                        from (select SS.schedule_id,
                                                        freq_interval, 
                                                        'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                                                        'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                                                        'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                                                        'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                                                        'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                                                        'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                                    from msdb..sysschedules ss
                                                where freq_type = 8
                                            ) as F
                                        where schedule_id = SJ.schedule_id
                                    )
            WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval) 
            WHEN (freq_type = 32) then (select  freq_rel + WDAY 
                                        from (select SS.schedule_id,
                                                        'freq_rel' = CASE(freq_relative_interval)
                                                                    WHEN 1 then 'First'
                                                                    WHEN 2 then 'Second'
                                                                    WHEN 4 then 'Third'
                                                                    WHEN 8 then 'Fourth'
                                                                    WHEN 16 then 'Last'
                                                                    ELSE '??'
                                                                    END,
                                                    'WDAY'     = CASE (freq_interval)
                                                                    WHEN 1 then ' Sun'
                                                                    WHEN 2 then ' Mon'
                                                                    WHEN 3 then ' Tue'
                                                                    WHEN 4 then ' Wed'
                                                                    WHEN 5 then ' Thu'
                                                                    WHEN 6 then ' Fri'
                                                                    WHEN 7 then ' Sat'
                                                                    WHEN 8 then ' Day'
                                                                    WHEN 9 then ' Weekday'
                                                                    WHEN 10 then ' Weekend'
                                                                    ELSE '??'
                                                                    END
                                                from msdb..sysschedules SS
                                                where SS.freq_type = 32
                                                ) as WS 
                                        where WS.schedule_id = SS.schedule_id
                                        ) 
        END AS Interval,
        CASE (freq_subday_type)
            WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(active_start_time)))+ convert(varchar(6),active_start_time),3,0,':')),6,0,':'),8)
            WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
            WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
            WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
            ELSE '??'
        END AS [Time],
        CASE SJ.next_run_date
            WHEN 0 THEN cast('n/a' as char(10))
            ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
        END AS NextRunTime
from msdb.dbo.sysjobs S
left join msdb.dbo.sysjobschedules SJ on S.job_id = SJ.job_id  
left join msdb.dbo.sysschedules SS on SS.schedule_id = SJ.schedule_id
order by S.name
Tara Kizer
Tara Kizer
August 30, 2016 14:43 PM

This will get all enabled jobs on your SQL Server and return, (Job Name, Schedule Name, Frequency, Sub Frequency, Schedule Time, Next Run Date / Time and Last Run Status)

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 myCTE
As(
    Select sched.name As 'scheduleName'
        , sched.schedule_id
        , jobsched.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'
        , Replicate('0', 6 - Len(sched.active_start_time)) 
            + Cast(sched.active_start_time As varchar(6)) As 'startTime'
        , Replicate('0', 6 - Len(sched.active_end_time)) 
            + Cast(sched.active_end_time As varchar(6)) As 'endTime'
        , 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

    Where sched.enabled = 1
)

Select Distinct job.name As 'Job Name'
    , sched.scheduleName 'Schedule Name'
    , sched.frequency 'Frequency'
    , sched.subFrequency 'Sub Frequency'
    , SubString(sched.startTime, 1, 2) + ':' 
        + SubString(sched.startTime, 3, 2) + ' - ' 
        + SubString(sched.endTime, 1, 2) + ':' 
        + SubString(sched.endTime, 3, 2) 
        As 'Schedule Time' -- HH:MM
    , SubString(sched.nextRunDate, 1, 4) + '/' 
        + SubString(sched.nextRunDate, 5, 2) + '/' 
        + SubString(sched.nextRunDate, 7, 2) + ' ' 
        + SubString(sched.nextRunTime, 1, 2) + ':' 
        + SubString(sched.nextRunTime, 3, 2) As 'Next Run Date / Time'
    , Case jhist.run_status
       When 0 Then 'Failed'
       When 1 Then 'Successful'
       When 2 Then 'Retry'
       When 3 Then 'Canceled'
       Else 'Unknown Status'
       End  as 'Last Run Status'    
From msdb.dbo.sysjobs As job
JOIN msdb.dbo.sysjobhistory as jhist on job.job_id = jhist.job_id and jhist.step_id = 0
Join myCTE As sched
    On job.job_id = sched.job_id
Where job.enabled = 1 
WEI_DBA
WEI_DBA
August 30, 2016 14:44 PM

This might be a good alternative script on how to get the detailed job schedule.. It also gives you the average duration per seconds of the job. Looking at the specific schedule/s of a job if enabled or disabled.

/* 
    Reference: https://www.sqlprofessionals.com/blog/sql-scripts/2014/10/06/insight-into-sql-agent-job-schedules/ 
    LastUpdate: 20/06/2018 - edited line 25 and line 64 
*/
SELECT   [JobName] = [jobs].[name]
        ,[Category] = [categories].[name]
        ,[Owner] = SUSER_SNAME([jobs].[owner_sid])
        ,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
        ,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
        ,[Description] = [jobs].[description]
        ,[Occurs] = 
                CASE [schedule].[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 SQL Server Agent starts'
                    WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
                    ELSE ''
                END
        ,[Occurs_detail] = 
                CASE [schedule].[freq_type]
                    WHEN   1 THEN 'One time only'
                    WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
                    WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
                        LEFT(
                            CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
                            CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
                            CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
                            CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
                            CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
                            CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
                            CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
                            LEN(
                                CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
                                CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
                                CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
                                CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
                                CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
                                CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
                                CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
                            ) - 1
                        )
                    WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
                    WHEN  32 THEN 'The ' + 
                            CASE [schedule].[freq_relative_interval]
                                WHEN  1 THEN 'First'
                                WHEN  2 THEN 'Second'
                                WHEN  4 THEN 'Third'
                                WHEN  8 THEN 'Fourth'
                                WHEN 16 THEN 'Last' 
                            END +
                            CASE [schedule].[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 Day' 
                            END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
                    ELSE 'N/A'
                END
        ,[Frequency] = 
                CASE [schedule].[freq_subday_type]
                    WHEN 1 THEN 'Occurs once at ' + 
                                STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
                    WHEN 2 THEN 'Occurs every ' + 
                                CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
                                STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
                                STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
                    WHEN 4 THEN 'Occurs every ' + 
                                CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
                                STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
                                STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
                    WHEN 8 THEN 'Occurs every ' + 
                                CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
                                STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
                                STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
                    ELSE ''
                END
        ,[AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration])
        ,[Next_Run_Date] = 
                CASE [jobschedule].[next_run_date]
                    WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
                    ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
                         STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
                END
FROM     [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
         LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
                 ON [jobs].[job_id] = [jobschedule].[job_id] 
         LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
                 ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
         INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK) 
                 ON [jobs].[category_id] = [categories].[category_id] 
         LEFT OUTER JOIN 
                    (   SELECT   [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + 
                                                                (([run_duration] % 10000) / 100 * 60) + 
                                                                 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
                        FROM     [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
                        WHERE    [step_id] = 0 
                        GROUP BY [job_id]
                     ) AS [jobhistory] 
                 ON [jobhistory].[job_id] = [jobs].[job_id];
GO
dco
dco
June 20, 2018 07:57 AM

Related Questions