April 1, 2010 at 5:25 am
Hi,
I am in need of query to find out the following fields of maintenance plan, Name of the maitenance , description of the maitenance,owner of the maitenance,taks of the maitenance,order for tasks execute of the maitenance,database name involved in the maintenance,backup path if its backup maintenance,scheduler name,scheduler time.
Thanks in Advance.
April 1, 2010 at 7:45 am
This is the closest query I could come up for your requirement:
SELECT a.name,
description,
owner,
b.subplan_name,
subplan_description,
e.database_name,
h.*,
d.name,
c.next_run_time
FROM msdb.dbo.sysmaintplan_plans AS a
INNER JOIN msdb.dbo.sysmaintplan_subplans AS b
ON a.id = b.plan_id
LEFT JOIN msdb.dbo.sysdbmaintplan_databases AS e
ON a.id = e.plan_id
LEFT JOIN msdb.dbo.sysjobschedules AS c
ON b.schedule_id = c.schedule_id
LEFT JOIN msdb.dbo.sysschedules AS d
ON c.schedule_id = d.schedule_id
OUTER APPLY ( SELECT TOP 1
line1,
line2,
line3
FROM msdb.dbo.sysmaintplan_log f
LEFT JOIN msdb.dbo.sysmaintplan_logdetail g
ON f.task_detail_id = g.task_detail_id
AND g.line1 LIKE '%Back%'
WHERE a.id = f.plan_id
ORDER BY f.end_time DESC
) AS h
ORDER BY a.name
I'm still unable to determine the backup path. It's a starting point, however.
-- Gianluca Sartori
April 2, 2010 at 5:41 am
Hi,
Thanks for your reply.
But i could not find the filed of maintenance steps "order for tasks execute". it help me to find out in which order it has been createded in the maintenance plan.
Thanks in Advance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply