sql-server - 确定 SQL Server 计划在一段时间内何时触发？

Question

在我管理的系统中，有多个 SQL Server 代理作业根据 dbo.sysschedules 中定义的时间表运行。

我想在我的应用程序上创建一个日历 View ，该 View 将显示每个作业在给定时间范围内计划运行的时间。例如，如果我的计划设置为在该月的第一个和第四个星期一上午 8:00 运行，那么考虑到当月的时间跨度 (4/2013)，我希望获取日期“4/1”/2013 上午 8:00，2013 年 4 月 22 日上午 8:00”返回。

有没有人找到实现这一目标的方法？我知道我可以阅读时间表定义并务实地解决这个问题，但我很好奇在我自己陷入困境之前是否有人已经想出了解决方案。

谢谢!

Answer 1

这些链接看起来会让您走上正确的道路...

Generate SQL Agent Job Schedule

Accessing SQL Server Agent Data

这是我从 mssqltips.com 更改的解决方案。

    WITH next_run_time AS
    (       
    SELECT      sJOBSCH.schedule_id AS  [ScheduleID]            
                ,[sJOB].[name] AS [JobName]
                ,CASE 
                WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
                ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':')
                AS DATETIME)
                END AS [LastRunDateTime]
                , CASE [sJOBSCH].[NextRunDate]
                WHEN 0 THEN NULL
                ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':')
                AS DATETIME)
                END AS [NextRunDateTime]
    FROM        [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN 
    (
    SELECT      [job_id]
                ,schedule_id
                ,MIN([next_run_date]) AS [NextRunDate]
                ,MIN([next_run_time]) AS [NextRunTime]
    FROM        [msdb].[dbo].[sysjobschedules]
    GROUP BY    [job_id],schedule_id
    )       AS [sJOBSCH]

    ON      [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN 
    (
    SELECT      [job_id]
                ,[run_date]
                ,[run_time]
                ,ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC

    )           AS RowNumber

    FROM        [msdb].[dbo].[sysjobhistory]
    WHERE       [step_id] = 0
    )       AS  [sJOBH]

        ON      [sJOB].[job_id] = [sJOBH].[job_id]
    AND         [sJOBH].[RowNumber] = 1
    )

    , Occurrence AS
    (
    SELECT      schedule_id AS [ScheduleID]
                ,[schedule_uid] AS [ScheduleUID]
                ,[name] AS [ScheduleName]
                ,CASE [enabled]
                WHEN 1 THEN 'Yes'
                WHEN 0 THEN 'No'
                END AS [IsEnabled]
                ,CASE 
                WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
                WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
                WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
                WHEN [freq_type] = 1 THEN 'One Time'
                END [ScheduleType]
                ,CASE [freq_type]
                WHEN 1 THEN 'One Time'
                WHEN 4 THEN 'Daily'
                WHEN 8 THEN 'Weekly'
                WHEN 16 THEN 'Monthly'
                WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
                WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
                WHEN 128 THEN 'Start whenever the CPUs become idle'
                END [Occurrence]
                ,CASE [freq_type]
                WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
                WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                        + ' week(s) on '
                        + 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
                WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                             + ' of every '
                             + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
                WHEN 32 THEN 'Occurs on '
                         + CASE [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 [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 ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                         + ' month(s)'
                END AS [Recurrence]
                ,CASE [freq_subday_type]
                WHEN 1 THEN 'Occurs once at ' 
                        + STUFF(
                     STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                    , 3, 0, ':')
                                , 6, 0, ':')
                WHEN 2 THEN 'Occurs every ' 
                        + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                        + STUFF(
                       STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                    , 3, 0, ':')
                                , 6, 0, ':')
                        + ' & ' 
                        + STUFF(
                        STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                    , 3, 0, ':')
                                , 6, 0, ':')
                WHEN 4 THEN 'Occurs every ' 
                        + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                        + STUFF(
                       STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                    , 3, 0, ':')
                                , 6, 0, ':')
                        + ' & ' 
                        + STUFF(
                        STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                    , 3, 0, ':')
                                , 6, 0, ':')
                WHEN 8 THEN 'Occurs every ' 
                        + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                        + STUFF(
                        STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                    , 3, 0, ':')
                                , 6, 0, ':')
                        + ' & ' 
                        + STUFF(
                        STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                    , 3, 0, ':')
                                , 6, 0, ':')
                END [Frequency]
                ,STUFF(
                    STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
                        , 8, 0, '-') AS [ScheduleUsageStartDate]
                ,STUFF(
                STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
                    , 8, 0, '-') AS [ScheduleUsageEndDate]
                ,[date_created] AS [ScheduleCreatedOn]
                ,[date_modified] AS [ScheduleLastModifiedOn]
    FROM        [msdb].[dbo].[sysschedules]

    )

    Select      nrt.JobName,nrt.LastRunDateTime,nrt.NextRunDateTime
                ,o.Occurrence,o.Recurrence
    From        next_run_time AS nrt
    Inner Join  Occurrence o
        ON      nrt.ScheduleID = o.ScheduleID

以及我的这个查询的结果。

作业名称===========syspolicy_purge_history

最后运行日期时间====4/23/13 19:47

NextRunDateTime====4/24/13 2:00

发生=========每天

重复=========每 1 天发生一次