Tuesday, November 24, 2015

Query SQL Server Maintenance Plan details

I recently inherited a couple of servers, and am in the process of assessing them, and cleaning things up.  Today I was viewing job logging, and found this in the output of a failed job:

     Could not load package "\Maintenance Plans\RumbaKillListCSV" because of error 

RumbaKillList?  Maintenance plans?  I didn't know about either of these things!  As I said, I just inherited these servers... it looks like I've got a lot more digging to do.  For starters, I wrote this to quickly list details regarding any maintenance plans that may exist.

     /* quick details regarding any maintenance plans you've inherited */
            mp.name [MaintenancePlan],
            mp.[description] [Description],
            mp.[owner] [Owner],
            sp.subplan_name [Subplan],
            sp.subplan_description [SubplanDescription],
            sj.name [Job],
            sj.[description] [JobDescription],
            sj.[enabled] [IsEnabled]
            msdb..sysmaintplan_plans mp INNER JOIN msdb..sysmaintplan_subplans sp
              ON mp.id = sp.plan_id INNER JOIN msdb..sysjobs sj
                ON sp.job_id = sj.job_id

Not much, just a quick look to see what plans are there.  See that 'IsEnabled' -- this is just something to let you know whether or not the maintenance plan's corresponding Agent job is enabled.

Here's the output for just this plan:

You can expand upon the output, if you'd like.  Take a look at this from MSFT, for more detail  on these and the other Maintenance Plan tables:

No comments:

Post a Comment