1

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?

|improve this question
2

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
|improve this answer
  • ,i learn will learn alot from this script. – kombo Oct 31 '13 at 8:45
  • I was looking for one that could give me the next date Fullbackup should run – kombo Oct 31 '13 at 8:46
  • @kombo, this script will give you the next date and start time for any job in SQL Server Agent. The next day and time the job will run are defined by the Start Date and Start Time columns. This requires however that you have actually created a schedule for your fullbackup maintenance plan. – Craig Efrein Nov 4 '13 at 8:08
  • ,script i does not show the next date for me. – kombo Nov 4 '13 at 13:24
  • @kombo, can you please include the output of the script in your question? – Craig Efrein Nov 4 '13 at 13:40
0

USE

  • select j.name, s.next_run_date, s.next_run_time from sysjobs j inner join sysjobschedules s on j.job_id = s.job_id

  • Select jobsched.next_run_date, jobsched.next_run_time, sched.name, sched.,jobsched. FROM msdb.dbo.sysschedules AS sched inner Join msdb.dbo.sysjobschedules AS jobsched ON sched.schedule_id = jobsched.schedule_id

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.