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 http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/
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 sched.name 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'
End
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 +
Case
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'
End
+
Case
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'
End
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)) +
Case
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 p.name 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'
, job.name 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 p.id = 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 p.id = 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!
