Is there an easier way to get a listing of all your
maintenance plans, what they do, and when they are
scheduled to occur - in a multiserver environment - aside
from pulling up enterprise manager for each server and
writing it all down? I would think these would be defined
in the system information and I could query it out but I
can't find anything about it in BOL or in searching KB and
newsgroups. Help please?
Thanks,
KevinJust one of the many problems with maintenance plans is that some of the
data is stored in the MP system tables, some is stored in Job system tables
and some is actually parsed from the command strings in the job steps. Here
is a query to list all jobs with their schedules, descriptions etc. You
could expand it a bit to join with the MP system tables to get just the ones
associated with the MP's.
Maintenance Plan system tables
--
sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
sysdbmaintplans
select a.[Name],a.[Enabled] AS [J_E],
b.[Enabled] AS [S_E],
case b.freq_type
when 1 then 'Run Once'
when 4 then 'Runs Daily'
when 8 then 'Every Week'
+ case freq_interval & 2 when 2 then ' on Mondays' else '' end
+ case freq_interval & 4 when 4 then ' on Tuesday' else '' end
+ case freq_interval & 8 when 8 then ' on Wednesday' else '' end
+ case freq_interval & 16 when 16 then ' on Thursday' else '' end
+ case freq_interval & 32 when 32 then ' on Friday' else '' end
+ case freq_interval & 64 when 64 then ' on Saturday' else '' end
+ case freq_interval & 1 when 1 then ' on Sunday' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval)
when 32 then 'Mthly ' + case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case b.freq_interval
when 1 then ' on Monday'
when 2 then ' on Tuesdsay'
when 3 then ' on Wednesday'
when 4 then ' on Thursday'
when 5 then ' on Friday'
when 6 then ' on Saturday'
when 7 then ' on Sunday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup'
when 128 then 'Idle'
else 'Err'
end as schedule,
case b.freq_subday_type
when 1 then 'Runs at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency,
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6) ,3 ,2) + ':'+
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time),6) ,5 ,2)
as start_at,
case freq_subday_type
when 1 then NULL
else
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_end_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,3 ,2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,5 ,2)
end as end_at,
a.[Description]
from [msdb].[dbo].[sysjobs] AS a INNER JOIN [msdb].[dbo].[sysjobschedules]
AS b
ON a.[Job_ID] = b.[Job_ID]
ORDER BY a.[Name]
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:12ecb01c44355$738f2a60$a001280a@.phx.gbl...
> Is there an easier way to get a listing of all your
> maintenance plans, what they do, and when they are
> scheduled to occur - in a multiserver environment - aside
> from pulling up enterprise manager for each server and
> writing it all down? I would think these would be defined
> in the system information and I could query it out but I
> can't find anything about it in BOL or in searching KB and
> newsgroups. Help please?
> Thanks,
> Kevin
The sproc currently set nocount and ansi_warnings so we'll give quoted_identifiers a try. Thanks for the suggestion.|||