Log de execução do Maintenance Plan

Esse script facilita muito para saber o log de erro na execução de um Maintenance Plan.


With UltimaExecucao as (
Select A.Plan_Id, Max(A.Start_Time) as DataExec
from msdb..sysmaintplan_log A
Inner Join msdb..sysmaintplan_plans B On A.Plan_Id = B.id
Where B.name = 'OrgDiario' --Aqui vai o nome do seu MP
Group by A.Plan_Id)
Select C.Name,
C.Owner,
Case when D.succeeded = 1 then 'True' else 'False' End as Succeeded,
D.line1,
D.line2,
D.line3,
D.line4,
D.line5,
D.start_time,
D.end_time,
D.error_number,
D.error_message
From UltimaExecucao A
Inner Join msdb..sysmaintplan_log B On A.Plan_Id = B.Plan_Id
And A.DataExec = B.start_time
Inner Join msdb..sysmaintplan_plans C On C.id = A.Plan_Id
Inner Join msdb..sysmaintplan_logdetail D On D.task_detail_id = B.task_detail_id