Maintenance Task Failed - how to log the error

  • I've a Multi step Maintenance plan of 5 steps called "Reindex". This automatically created an Agent job called "Reindex..Subplan_1"

    There's an email alert on failure of the Agent job.

    Is there any place in maintenance task design to put an alert in or do I always have to use add the alert to Agent task it created.

    Maintenance plan History:

    The plan history job shows success

    The log file created shows 4 out of the 5 steps. No Error message in the logfile.

    I know the last, 5th step (update statistics) is missing so I know update statistics failed.

    SQL Agent History:

    I got an Email Alert of failure. Email just says subplan1 failed.

    If I look at the Reindex..Subplan_1 in the SQL Agent jobs it does shows an error happened.

    But lists the output of Step one 1  (not the failed step 5 ) guess its because msdb.dbo.sysjobhistory.message is 4000 chars max. There is no space to show the error.

    How do I get it to log the error, so next time it happens I know what it is.

    More a question about logging than a specific failure. Is this because all my 5 tasks in the maintenance plan are in the same subtask?


  • Honestly, these are one more of the many the reasons to stop using maintenance plans.

    For starters, you have little control over what it's doing.  In SQL 2014, there is no setting to limit the rebuilds to indexes over a certain percentage.

    You can adjust the reporting and logging in the maintenance plan.

    But, do yourself a favor and get rid of the maintenance plans.   Use Ola Hallengrens maintenance solution. It's not perfect, but it is a far better option than the maintenance plans.

    There is one issue with the reindexing.  It is still set up by default to use the 5%/30% for reorg and rebuild.  These are no longer best practices set forth by Microsoft.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:

  • Thanks

    I set "log extended information.." on the maintenance task - didn't know this existed

    In the subplan job in Agent I set "include step output in history"

    Next time it happens hopefully will get the error.

    Find it confusing what to set where.

    How would you handle file tasks e.g. delete backup files older than 3 days etc in Agent Jobs/SQL. The tidy up routines of msdb are handy


Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply