How to query SQL 2005 Maintenance Job Information
I am trying to figure out how to query the Maintenance Plan Jobs and their associated detail (sub plans, schedule detail, etc.). I read a previous article called "SQL query to show all databases in a Maint Plan" however the entire script is for SQL 2000.
I know I can get the list of the plan names from:
SELECT * FROM sysmaintplan_plans (this is a view)
I can get schedule info from:
SELECT * FROM [MSDB].[dbo].[sysschedules
And there are other tables such as "msdb.dbo.sysmaintplan_sub
So what I want is to be able to query or script so I can obtain a list of all the maintenance jobs on a SQL server with the schedule details for each.
I know I can get the list of the plan names from:
SELECT * FROM sysmaintplan_plans (this is a view)
I can get schedule info from:
SELECT * FROM [MSDB].[dbo].[sysschedules
And there are other tables such as "msdb.dbo.sysmaintplan_sub
So what I want is to be able to query or script so I can obtain a list of all the maintenance jobs on a SQL server with the schedule details for each.
ASKER
Yes, it is a mixture of SQL, Quest LiteSpeed, etc. Definitely not easy to get at. I might be able to get at what I need in one of the tables. I don't need to granular, but do need at least the name of the job/plan and the schedule info...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey thanks Sharath! That is the closest I have seen to what I need. I can script it out more to interpret the values (freq type, freq_subdat_type..etc)...
ASKER
The solutions was as complete as you can get. This will give me what I need and I can parse out the jobs that are not what I need. Thanks!!!!
If you had dont this with T-SQL; then you could have looked at the system tables in MSDB.
I belive you're looking at in this instance are:
SysJobs
SysJobHistory
SysJobSchedules
HTH