One DBA's Ongoing Search for Clarity in the Middle of Nowhere


Yet Another Andy Writing About SQL Server

Wednesday, May 7, 2014

Help - I Need a List of my Maintenance Plan Jobs *and* Schedules!

UPDATE - Jason Brimhall (blog/@sqlrnnr) pointed out after reading this post that there is a more inline way to do a lot of this work as shown in his post here.  Jason's post relates to SSRS reports (very similar logic since they also are SQL Agent jobs) and could easily be applied to this same maintenance plan problem - thanks Jason!

I have written over and over about how I dislike SQL Server's canned SSIS Maintenance Plans and how I strongly prefer Ola Hallengren's Maintenance Solution.  Another popular option for intelligent index maintenance are the scripts from Michelle Ufford (blog/@sqlfool), which also perform intelligent re-indexing.  The key advantage of Ola's code to me is that it is a full maintenance suite, handling backups, CHECKDB, *and* index maintenance.

All of that aside, as a Managed Services DBA I still have to frequently deal with Maintenance Plans and their repercussions on my innocent unsuspecting clients.

I recently received a request from one of my clients for all of the maintenance plan information and their related jobs for backup tasks.  I had a script to return maintenance plan information (using the updated tables and views present from SQL Server 2005+) but I didn't have an easy way to reference the jobs and job schedules - sure I knew how to pull basic info from sysjobs and sysjobschedules, but doing so is a complete mess of bit-wise mathematics to decipher the multi-values in a single field (bad Microsoft!) that is prevalent throughout the job tables and views.

A little Google-Fu brought me to Michelle's blog and an amazing entry where she already has all of the bit-wise-ness (bit-wisdom?) worked out in a very elegant query to pull the relevant information.  Her original blog post is about generating disable statements for the jobs, but I slightly modified her query to return a few extra fields and then hacked it together with my pre-existing maintenance plan information query to return the data I needed for my client:
Backup information from maintenance plans - jobs, schedules, etc.

Andy Galbraith @DBA_ANDY

MSSQL 2005+

Heavily borrows from
for the original job schedule CTE and base query - thanks Michelle!

I modified Michelle's original query slightly and then added
the maintenance plan information to match the jobs to their
parent maintenance plans.

The filter that makes the query relevant to backup subplans is:

"and smpld.line1 like '%Back Up%'"

Commenting out or removing this line will display information about
all maintenance plan subplans and their enabled jobs

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 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'
        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 +
        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'
        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'
    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)) +
        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 as 'Maintenance_Plan'
, p.[owner] as 'Plan_Owner'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, As 'Job_Name'
, sched.frequency as 'Schedule_Frequency'
, sched.subFrequency as 'Schedule_Subfrequency'
, 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'
Note: the sysjobschedules table refreshes every 20 min,
so Next_Run_Date may be out of date
From msdb.dbo.sysjobs As job
Join myCTE As sched
On job.job_id = sched.job_id
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on = sp.plan_id
JOIN msdb.dbo.sysjobschedules sjs
ON job.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id
join msdb.dbo.sysmaintplan_log smpl
on = smpl.plan_id
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id
and smpld.line1 like '%Back Up%'
where job.[enabled] = 1
and smpld.line3<>''
Order By Next_Run_Date;
The output of the query will look like this (wrapped so it could be inserted at a readable font):
As can be seen from these results, this example instance has four Maintenance Plans, each with one sub-plan related to backups.  The "FULL SQLBKUP_DAILY_AllButSunday" maintenance plan runs weekly every day but Sunday once each day at 10:45pm.

Shout out again to Michelle for the CTE that compiles the job schedule bit-wise information - you saved me (and I'm sure many others) a lot of busywork!


Hope this helps!


  1. Thanks for the shout out Andy

  2. Thanks mate! This is pretty slick report. Just what I need in order to ease the pain of taking over a dozen of not-really-maintained Sql Servers.