SQL Server maintenance plans are a great way to set up scheduled maintenance on your databases. With a few quick mouse clicks you have the jobs automatically created for you. However, finding out which jobs run when for which maintenance plans can be a royal pain in the behind if you have lots of plans running. Plus, if you’ve ever inherited a server from another DBA and need to figure out how maintenance has been set up, you know what a time-consuming process this is, especially if plans and jobs have been renamed. This script will match all your maintenance plans on a particular server with the jobs set up for it, as well as provide summary information as to when each job’s schedules run, with what frequency, and what’s enabled and disabled, etc. This script really saves hours. It was written for SQL Server 2000 but should also run in 7.0.
Author: Jeffrey Bane
Script Contents:
USE MSDB
GO
SET NOCOUNT ON
create table #temp
([Job Name] varchar(128),
[Job Enabled] varchar(3),
[Plan Name] varchar(128),
[Job Type] varchar(50),
[Databases] varchar(1000),
[Schedule Name] varchar(128),
[Schedule Enabled] varchar(3),
[Schedule] varchar(20),
[Interval] varchar(200),
[Start Date] char(10),
[End Date] char(10)
)
declare @job_name varchar(128), @JobEnabled varchar(3),
@plan_name varchar(128), @ScheduleName varchar(128), @ScheduleEnabled varchar(3),
@job_type varchar(50), @plan_id uniqueidentifier,
@dbString varchar(1000), @freq_interval int, @frequency varchar(20),
@Interval varchar(200), @freq_type int, @freq_recurrence_factor int,
@freq_relative_interval int, @freq_subday_type int, @freq_subday_interval int,
@active_start_time int, @active_end_time int,
@xActive_start_time varchar(8), @xActive_end_time varchar(8),
@startdate char(10), @enddate char(10)
declare maintCursor cursor for
select sj.name as job_Name,
case when sj.enabled = 1 then 'Yes'
when sj.enabled = 0 then 'No'
end
as JobEnabled,
sd.plan_id, sm.plan_name, sch.name,
case when sch.enabled = 1 then 'Yes'
when sch.enabled = 0 then 'No'
end
as ScheduleEnabled,
sch.freq_interval,
case when js.command like '%-BkUpDB%' then 'Database backup job'
when js.command like '%-BkUpLog%' then 'Transaction log backup job'
when js.command like '%-CkDB%' then 'Integrity checks job'
when js.command like '%-RebldIdx%' then 'Optimizations job'
end
as [Job_Type],
case when sch.freq_type = 16 or sch.freq_type = 32 then 'Monthly job'
when sch.freq_type = 8 then 'Weekly Job'
when sch.freq_type = 4 then 'Daily Job'
end
as [Schedule],
sch.freq_type, sch.freq_recurrence_factor, sch.freq_relative_interval,
sch.freq_subday_type, sch.freq_subday_interval,
sch.active_start_time, sch.active_end_time,
sch.active_start_date, sch.active_end_date
from sysjobs sj inner join sysdbmaintplan_jobs sd
on sj.job_id = sd.job_id inner join sysdbmaintplans sm
on sd.plan_id = sm.plan_id inner join sysjobsteps js
on sj.job_id = js.job_id inner join sysjobschedules sch
on sch.job_id = sj.job_id
order by sj.name
open maintCursor
fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate
while @@fetch_status = 0
begin
select @xActive_start_time = replicate('0', 6 - len(@active_start_time)) + cast(@active_start_time as varchar)
select @xActive_end_time = replicate('0', 6 - len(@active_end_time)) + cast(@active_end_time as varchar)
select @xActive_start_time = substring(@xActive_start_time, 1, 2) + ':' + substring(@xActive_start_time, len(@xActive_start_time) - 3, 2) + ':' + right (@xActive_start_time, 2)
select @xActive_end_time = substring(@xActive_end_time, 1, 2) + ':' + substring(@xActive_end_time, len(@xActive_end_time) - 3, 2) + ':' + right (@xActive_end_time, 2)
select @dbString = ''
select @Interval = ''
select @dbString = @dbString + database_name + ', ' from sysdbmaintplan_databases
where plan_id = @plan_id
if @freq_type = 4
begin
select @Interval = @Interval + 'Every ' + cast(@freq_interval as varchar) + ' day(s).'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
if @freq_type = 8
begin
select @Interval = @Interval + 'Every ' + cast(@freq_recurrence_factor as varchar) + ' week(s) on'
if @freq_interval & 1 = 1 select @Interval = @Interval + ' SUN'
if @freq_interval & 2 = 2 select @Interval = @Interval + ' MON'
if @freq_interval & 4 = 4 select @Interval = @Interval + ' TUE'
if @freq_interval & 8 = 8 select @Interval = @Interval + ' WED'
if @freq_interval & 16 = 16 select @Interval = @Interval + ' THU'
if @freq_interval & 32 = 32 select @Interval = @Interval + ' FRI'
if @freq_interval & 64 = 64 select @Interval = @Interval + ' SAT'
select @Interval = @Interval + '.'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
if @freq_type = 16
begin
select @Interval = 'Day ' + cast(@freq_interval as varchar) + ', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
if @freq_type = 32
begin
select @Interval = 'The ' +
case cast(@freq_relative_interval as varchar)
when '1' then 'first '
when '2' then 'second '
when '4' then 'third '
when '8' then 'fourth '
when '16' then 'last '
end
+
case cast(@freq_interval as varchar)
when '1' then 'Sunday'
when '2' then 'Monday'
when '3' then 'Tuesday'
when '4' then 'Wednesday'
when '5' then 'Thursday'
when '6' then 'Friday'
when '7' then 'Saturday'
when '8' then 'day'
when '9' then 'weekday'
when '10' then 'weekend day'
end
+
', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
Select @StartDate = substring(@StartDate, 5, 2) + '/' + substring(@StartDate, 7,2) + '/' + substring(@StartDate, 1, 4)
Select @EndDate = substring(@EndDate, 5, 2) + '/' + substring(@EndDate, 7,2) + '/' + substring(@EndDate, 1, 4)
if right(@EndDate, 4) = '9999'
Select @EndDate = 'None'
insert into #temp
([Job Name], [Job Enabled], [Plan Name], [Job Type], [Databases], [Interval], [Schedule Name], [Schedule Enabled], [schedule], [Start Date], [End Date])
values
(@job_name, @JobEnabled, @plan_name, @job_type, @dbString, @Interval, @ScheduleName, @ScheduleEnabled, @frequency, @StartDate, @EndDate)
fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate
end
close maintCursor
deallocate maintCursor
select [Plan Name], [Job Name], [Job Enabled], [Job Type], left(Databases, len(databases)-1) as Databases,
[Schedule Name], [Schedule Enabled], Schedule as [Schedule Type], Interval, [Start Date], [End Date]
from #temp
order by [plan name], [Job Type], [Schedule Name]
drop table #temp
