January 10, 2006 at 3:30 pm
To all,
I am using SQL 2000 and I have a lot of maintenance plans. I am looking for a way to "document" these plans. By that I mean, I want to run a query to get a report about each plan. such as When it was created, what database(s) is it related to and what is it's function. i.e. transaction log backup, database backup, and what time it is scheduled to execute etc...
Any help is greatly appreciated.
Thanks
Gary
January 10, 2006 at 9:35 pm
There is a stored proc in MSDB called sp_Help_MaintenancePlan, but it doesn't look real helpful. The query below gives information about the maintenance plans, but not the schedules:
SELECT sj.job_id,smp.plan_name, substring(sj.command,80,100)as command ,
sd.database_name , smp.date_created as [plan created]
FROM msdb.dbo.sysjobsteps sj join
msdb.dbo.sysdbmaintplan_jobs sp
on
sj.job_id = sp.job_id
join msdb.dbo.sysdbmaintplan_databases sd
on sp.plan_id = sd.plan_id join msdb.dbo.sysdbmaintplans smp
on sd.plan_id = smp.plan_id
You could run sp_help_jobschedule for every job to get the schedule info, but you will have to translate what each column means. There is probably a good resource somewhere that explains each column.
Maybe this will get you started...
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 11, 2006 at 10:49 am
Aunt Kathi,
I have been looking in the MSDB and at the tables you mentioned. If I can create a query that gives me what I want I will post it here.
Thank You
Gary
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply