Today, it's about maintenance plan and it's history
Often we run into situations where jobs executing maintenance plan does not show actual error that caused the failure. And more often than not in such scenarios browsing through maintenance plans history works like a charm..
Recently a friend of mine returning from his long vacation and ran into similar situation where his sql agent job didn't provide me sufficient information about the root cause of failure. And to make matter worse they had limitation put on the agent history retention which did not help either. Thankfully he had error notification setup which helped him listing out the names of the job that had failed.
Given below is what I wrote to help him out .. there might be easier way than this so fill me in if you happen to know about it
SELECT
mp.name AS MaintenancePlan
,ml.start_time
,ml.end_time
,ml.succeeded
,mld.error_message
FROM msdb..sysmaintplan_logdetail mld
INNER JOIN msdb.dbo.sysmaintplan_log ml
ON ml.task_detail_id = mld.task_detail_id
INNER JOIN msdb..sysmaintplan_plans mp
ON mp.id = ml.plan_id
WHERE mp.name = 'Maintenance Plan name' AND ml.succeeded = 0
ORDER BY ml.start_time desc
Often we run into situations where jobs executing maintenance plan does not show actual error that caused the failure. And more often than not in such scenarios browsing through maintenance plans history works like a charm..
Recently a friend of mine returning from his long vacation and ran into similar situation where his sql agent job didn't provide me sufficient information about the root cause of failure. And to make matter worse they had limitation put on the agent history retention which did not help either. Thankfully he had error notification setup which helped him listing out the names of the job that had failed.
Given below is what I wrote to help him out .. there might be easier way than this so fill me in if you happen to know about it
SELECT
mp.name AS MaintenancePlan
,ml.start_time
,ml.end_time
,ml.succeeded
,mld.error_message
FROM msdb..sysmaintplan_logdetail mld
INNER JOIN msdb.dbo.sysmaintplan_log ml
ON ml.task_detail_id = mld.task_detail_id
INNER JOIN msdb..sysmaintplan_plans mp
ON mp.id = ml.plan_id
WHERE mp.name = 'Maintenance Plan name' AND ml.succeeded = 0
ORDER BY ml.start_time desc
No comments:
Post a Comment