Generate maintenace plan detail on server

Question

Hi, Please give some system table details or SQL query to fetch maintenance plan details on server through SQL QUERY.

Required table results:-

ServerName
DBName
DBRecoveryMode
MaintenancePlanName
SubPlan
Status
Description
JobName
Schedule
JobStatus

I write query that gives information about available maintenance plan details but want to fetch few give above column also.

SELECT name,subplan_name,subplan_description
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
Order by name,subplan_name

Answer 1

The following query will give you the details of the maintenance plan and any job and schedule details that are available for it.

SELECT  s.name AS MaintenancePlanName,
        sp.subplan_name AS SubplanName,
        subplan_description AS SubplanDescription,
        sj.name AS JobName,
        sj.enabled AS JobStatus,
        ss.name AS ScheduleName
FROM    msdb.dbo.sysmaintplan_plans AS s
LEFT JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id = s.id
LEFT JOIN msdb.dbo.sysjobs AS sj ON sj.job_id = sp.job_id
LEFT JOIN msdb.dbo.sysschedules AS ss ON sp.schedule_id = ss.schedule_id
ORDER BY s.name,
        sp.subplan_name

The only issue here is that you cannot interrogate the maintenance plan to find the databases you would be working on (as far as I know).

Maintenance plans are actually SSIS packages that are stored inside SQL Server. You can find the binary data for the package in the system table msdb.dbo.sysssispackages. The following query returns maintenance plan packages from the package store:

SELECT  *
FROM    msdb.dbo.sysssispackages AS s
WHERE   packagetype = 6

The actual package is in the column packagedata and is a binary stream of the package. I don't think that you can easily query this to get the data you want - you will probably need to stream it out of sql server and into an ssis package file to work with it.

Generate maintenace plan detail on server

Follow this question

Related Questions