Sql-server – When next will the fullback up run

maintenance-planssql-server-2008-r2

I created a maintenance plan to run full backups on a SQL Server 2008 R2.

The schedule runs every last Thursday of the month. Sometimes due to some unknown reason, such as electric outage. When the backup is successful, I verify it by checking SQL Server report for backup and restore events.

Now my question is: does SQL Server provide a way to check the next time the maintenance plan will run based on the the maintenance plan schedule? Also is there is a script to run to show me this would be appreciated?

Best Answer

This script will provide you with detailed information on SQL Server Agent Jobs and their schedules. This doesn't include any scheduled task that may exist in Scheduled Tasks for 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]

enter image description here

Update 11/4/2013

Here is another script that associates plans with their associated jobs

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

Best Answer

Related Solutions

Sql-server – How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks

Sql-server – Maintenance Plan Job running at additional unscheduled times

Related Question