SQL Server job error on email notification

Question

I have configured a database email, operators, and such on my SQL managed instance, to receive an email when a job fails.

In the email, we get something like this "The yyy_job failed on step 3".

But my question is... Is there a way to add the error message on the body of the email? I've been searching for this, but can't fine a suitable answer.

Thank you in advance

As far as I know you cant modify the email messages the agent sends out. Depending on what it is your jobs are doing you could embed the sending of the message inside the job and have it contain whatever you need. If we had more information we could provide alternative solutions. — Chris Albert, May 31 '22 at 19:18
Like the others, there doesn't appear to be a way. You could set up your own monitoring, or use a different application to do so. SQL Monitor by Redgate (which we use at the office, I have no affiliation with them) reports the failure reason in the email, if you configure it to notify you when a job fails. The email look like [this](https://i.stack.imgur.com/3mLRT.png), for your reference. — Thom A, Jun 01 '22 at 08:24
Thank you @ChrisAlbert for your response. the idea is not to refactor what we have right now. — Van Darth Vadden, Jun 01 '22 at 13:23
@Larnu I will take a look at the tool you recommended. Thanks — Van Darth Vadden, Jun 01 '22 at 13:24

score 1 · Accepted Answer · answered May 31 '22 at 19:19

1

As far as I know there's no way to add further details to the email notifications when a job fails.

The only way is to implement your own notification process.

https://www.sqlshack.com/reporting-and-alerting-on-job-failure-in-sql-server/

answered May 31 '22 at 19:19

Stewart

705
3
6

Thanks for your reply. How about the [msbd].[dbo].[sp_send_dbmail] SP? I see a property named @attach_query_result_as_file in this SP. But enableing it is not working for me. – Van Darth Vadden May 31 '22 at 19:37
sp_send_dbemail is geared toward scenarios such as sending a daily report to a list of recipients. Did you specify the query parameter when you tried, which is required for attach_query_result_as_file to have an effect? Regarding your question, what is the specific type of job are you running. Is is possible to catch any errors in your SQL scripts e.g. try catch blocks and log them to an error table? Then you could have a supporting job that queries recent errors and uses sp_send_dbmail to notify you of them, – Stewart May 31 '22 at 22:03
We have a Job that have steps. Each step calls an SP with a parameter. My Initial Idea was to append the message that is stored in msb.dbo.sysjobhistory in the email. But as you said, I would led me to build a custom notification process – Van Darth Vadden Jun 01 '22 at 13:21

Vladimir Baranov · Answer 2 · 2022-06-02T04:54:27.847

We have a similar set up. We have a SQL Server Agent job that consists of several steps.

I configured it in such a way that we receive notification email when the job starts and another email when it finishes. There are two versions of the final email - one for success, another for failure.

At the end of the job there are two final steps called "Email OK" and "Email FAIL". Note how each of the steps have their "On Success" and "On Failure" configured.

This is how "Email OK" and "Email FAIL" steps look like in our case:

In my case I simply have different subjects of the emails, so it is easy to filter in the email client.

You can write any extra T-SQL code to execute a query against msdb.dbo.sysjobhistory and include the relevant result into the email.

I will not write a complete query here, but I imagine it would look similar to my sketch below. If you need help with that, ask another question.

This is how you can use msdb.dbo.sp_send_dbmail to include the result of some query into the email text:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ABC'
    ,@recipients = 'abc@example.com'
    ,@subject = 'Some subject line'
    ,@body = @VarBody
    ,@body_format = 'TEXT'
    ,@importance = 'NORMAL'
    ,@sensitivity = 'NORMAL'
    ,@query = N'
        -- show latest entry in the log for your job
        SELECT TOP(1)
            message, ...
        FROM
            msdb.dbo.sysjobhistory
        WHERE
            job_id = ''your job ID''
        ORDER BY
            instance_id DESC; 
        '
    ,@execute_query_database = 'msdb'
    ;

Have a look at the documentation for a list of parameters for sp_send_dbmail. Example above inlines the query result. You can also attach it as a separate file.

SQL Server job error on email notification

2 Answers2