主页 / dba / 问题 / 52491
Accepted
kombo
Asked: 2013-10-31 03:51:56 +0800 CST

我的后卫下次什么时候上场

  • 772

我创建了一个维护计划以在 SQL Server 2008 R2 上运行完整备份。

该计划在每个月的最后一个星期四运行。有时由于一些不明原因，例如停电。当备份成功时，我通过检查 SQL Server 报告的备份和恢复事件来验证它。

现在我的问题是：SQL Server 是否提供了一种方法来检查下一次维护计划将根据维护计划时间表运行？还有一个脚本可以运行来告诉我这将不胜感激吗？

sql-server-2008-r2 maintenance-plans

2 个回答

  1. Best Answer

    此脚本将为您提供有关 SQL Server 代理作业及其计划的详细信息。这不包括 Windows 计划任务中可能存在的任何计划任务。

    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 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,
--  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]

    在此处输入图像描述

    2013 年 11 月 4 日更新

    这是另一个将计划与其相关工作相关联的脚本

    SELECT 
s.name as [PlanName],
spl.start_time AS [StartTime], 
spl.end_time AS [EndTime], 
spl.succeeded AS [Succeeded],
sysjobs.name as [JobName],
'Frequency' = CASE 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
FROM msdb.dbo.sysmaintplan_plans AS s 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id 
LEFT OUTER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id 
INNER JOIN msdb.dbo.sysjobs sysjobs on sysjobs.job_id = sp.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
    • 2

  2. 利用

    • 从 sysjobs 中选择 j.name、s.next_run_date、s.next_run_time j inner join sysjobschedules s on j.job_id = s.job_id

    • 选择 jobsched.next_run_date、jobsched.next_run_time、sched.name、sched。，工作计划。FROM msdb.dbo.sysschedules AS sched inner 加入 msdb.dbo.sysjobschedules AS jobschedules ON sched.schedule_id = jobschedule_id

    • 0

相关问题