Querying SQL Server maintenance plans

A request came in to enumerate active maintenance plans and associated jobs across a range of servers.
Here is the query I developed for the purpose.

	p.name as 'Maintenance Plan'
	,p.[description] as 'Description'
	,p.[owner] as 'Plan Owner'
	,sp.subplan_name as 'Subplan Name'
	,sp.subplan_description as 'Subplan Description'
	,j.name as 'Job Name'
	,j.[description] as 'Job Description'  
from msdb..sysmaintplan_plans p
	inner join msdb..sysmaintplan_subplans sp
	on p.id = sp.plan_id
	inner join msdb..sysjobs j
	on sp.job_id = j.job_id
where j.[enabled] = 1

As you can see from the where clause – I am only interested in filtering for active jobs. I’m also only returning basic name information, I only really want to know what plans exist and what are the jobs associated with them. Other columns are available as you wish. If you really love resolving bitwise operators you might want to join to msdb..sysschedules and return schedule information 🙂

This entry was posted in SQL Server, T-SQL. Bookmark the permalink.

3 Responses to Querying SQL Server maintenance plans

  1. Claus B says:

    Nice, just what i needed

  2. Guys – Here I am on a Sunday Night in Sydney and did I need this scripts. Cheers to this – appreciate the work. Was hunting through a load of MP’s and needed some answers real quick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s