August 25, 2023 at 8:57 am
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?
Thanks
August 25, 2023 at 3:50 pm
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:
http://www.sqlservercentral.com/articles/61537/
August 30, 2023 at 9:41 am
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