Identifiy the failed job steps when a job has failed
CONTEXT: When looking at a job failed alert, we would like to pinpoint why it has failed.
PROBLEM: SQL Monitor doesn't indentify the failed steps - we have to go to SSIS to get that information.
• Add this information to the "Job failed" alert
• Provide a query to make retrieving this data more convenient
would it be possible to just add the error message like you see in the job history? It would great to see if it's a deadlock, issue with the code, etc... without having to open SSMS
You're probably right that a fraction of these votes are about the job step failure alert - but I have no way of splitting these votes out (this is why multi-part suggestions are a bit of a headache). We'll definitely monitor the uptake of the new suggestion you've created (thank you for that!), and prioritise it accordingly if it gains traction, also considering the number of votes from this suggestion.
OK Daniel, I can do that. But since Allen and me commented about "Job Step Failure alert", I have a feeling quite a few votes on this suggestion is meant for that. IMHO, not being alerted about such job step failures can be a big risk - a monitoring product should really cover that. Please strongly consider it for implementation.
For me, not being alerted about such job step failures it is a bigger pain than missing some extra error info (aka this ticket).
I urge everyone interested to vote on the new suggestion I made "Job step failure - trigger an alert, even if the job itself doesn't fail": http://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/11129463-job-step-failure-trigger-an-alert-even-if-the-j
I'd recommend opening a new suggestion about the "quit the job reporting failure" aspect, as it's quite different from the original suggestion.
Thanks for your feedback Allen.
Allen Dunn commented
The Job Step Failure alert is essential as currently jobs where the "quit the job..." option is not enabled you can miss failures.
I am aware thet the job history in SQL Server GUI does not show failed steps if the last step completes, but it would be ideal to not have to query all servers for failed steps when SQL Monitor could pick them up.
Thanks all for your suggestions.
The additional SSIS info would be VERY helpful, especially as the SSISDB query from SSMS is a SLUG and cumbersome and calls for a lot of clicking to drill down to the information - and I only have ONE PACKAGE in SQL2012 to monitor!
I forgot an important comment about job steps:
At the moment there is no alert in case a job step fails, but it is NOT set to "quit the job reporting failure"... ie. one can have a multi-step job where the desired behavior is that the steps execute no matter what happened in previous steps. In case one of those steps fails, an alert would be beneficial or even crucial.
Hey. The thing I'm missing most is the error message for the failed step. It's the message you see in job history GUI in SSMS for a particular job step. Of course there's other stuff there as well (step number, duration...).
For SQL 2012 job steps that run SSIS packets, one step further would be to include error messages from the SSISDB catalog, since all the error message on job step says is something like "go check SSISDB catalog for more details" :)
Hope that helps.
Hi, thanks for this suggestion. We're looking into this and will get back to you soon. In the meantime, let us know if you have any further details regarding what you consider to be the relevant information in the steps.