x

Generate maintenace plan detail on server

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
more ▼

asked Apr 19, 2012 at 07:46 AM in Default

Amardeep gravatar image

Amardeep
1.3k 87 88 89

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Apr 19, 2012 at 08:09 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x87
x33

asked: Apr 19, 2012 at 07:46 AM

Seen: 1253 times

Last Updated: Apr 19, 2012 at 08:09 AM