May 30, 2011 at 9:54 am
I'm trying to build some MI to extract data on multiple server maintenance plans. I've already found a number of helpful queries on this (example at the bottom)
But this query doesn't give me the list of databases this maintenance plan is used on, when it runs, what tasks (e.g index rebuild) the maintenance plan is doing (other than in plan/sub plan name).
Are there any other tables that will identify this data for me?
SELECT
ld.line1 AS [Line1],
ld.line2 AS [Line2],
ld.line3 AS [Line3],
ld.line4 AS [Line4],
ld.line5 AS [Line5],
ld.server_name AS [ServerName],
ld.start_time AS [StartTime],
ld.end_time AS [EndTime],
ld.error_number AS [ErrorNo],
ld.error_message AS [ErrorMessage],
ld.command AS [Command],
ld.succeeded AS [Succeeded]
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id
INNER JOIN msdb.dbo.sysmaintplan_logdetail AS ld ON ld.task_detail_id=spl.task_detail_id
WHERE sp.subplan_id=N'240B428A-192E-4FEC-BED7-B41701DF7D82'
AND s.id=N'57BB0D74-0891-4F46-AE91-A3DD28AAB5D5'
ORDER BY [StartTime] DESC
May 31, 2011 at 4:04 am
From what I can see the only way to pull this data out is between the database and opening the dtsx files as XML. Doesn't look like there's any easy way to build a semantic layer for some MI
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply