Maintenance Plan Information

  • 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

     

  • 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

  • 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