How can I have SQL Server email me the error details when a job fails?

Question

SQL Server lets you configure a job to send email alerts when it fails. This is a simple and effective way to monitor your jobs. However, these alerts do not include any detail--just a success or failure notice.

If a job fails, this is what a typical alert email will look like:

JOB RUN:        'DBA - Consistency Check Databases' was run on 8/14/2011 at 12:00:04 AM
DURATION:       0 hours, 0 minutes, 0 seconds
STATUS:         Failed
MESSAGES:       The job failed.  The Job was invoked by Schedule 2 (Nightly Before 
                Backup 12AM).  The last step to run was step 1 (Check Databases).

To determine the cause of the failure, you have to navigate to the instance in SQL Server Management Studio, find the job, and view its execution history. In a large environment it can be a pain to have to constantly do this.

The ideal alert email would include the failure reason upfront and let you get straight to working on the solution.

I am familiar with this solution to this problem. Does anyone have any experience with it? Its drawbacks are:

1. you have to add a new step every job you have, and
2. you have to pray that no-one messes up the alert proc, spDBA_job_notification

Has anyone come up with a better solution?

Answer 1

Something you might do that is just a thought, throwing ideas out...

Create a single job that periodically checks the job table in msdb to see if any jobs show as failed, that can be done with a good T-SQL query. Then you could go into the sysjobsteps table and see if an output log is set for the job. Have a stored procedure send an email attaching that file to it. You would be able to see exactly what the job did from beginning to failure without having to touch the server.

Then could also have PowerShell script check the event log for errors. It allows you to filter down a pretty good bit to get exactly what message types you are looking for. You could set that up as a SQL Agent job to run periodically. Then in the PowerShell script use the email cmdlet to send the message if it finds one.

Far fetched ideas here, just some I thought about.

Answer 2

I have experience with the aforementioned idea. It's good, but a better idea would be to do something like Shawn said.

What we did was to make a job that runs every 5 minutes and scans MSDB tables about job failures. For each job that had a failure we'd run the SP spDBA_job_notification with it's own ID, so the SP will scan the MSDB history steps for errors and email them all. From the SP documentation: "The stored procedure uses the job ID to query the msdb agent tables for the most recent error message for that job."

So instead of just changing every job, better create a single one that does all ;-).

Another idea is to set all jobs to write to Windows Event Viewer in case of errors/failures and read from there with extended proc xp_ReadErrorLog or an automatic tool, if you have that already in your network. For example we used HPOV to check any system issues and could configure a simple alert for all event viewer errors (no need for any custom job or procedure).

Answer 3

Give this a try and just plug in your variables as needed in TSQL. The key here is to put this as the very last step of each individual SQL agent job but each job step above it needs to go to NEXT STEP whether it is FAILURE or SUCCESS... Works for me just fine for the most part but please do report any issues you run across. We're on SQL Server 2008 R2 so this is where it's used where I've set it up currently.

SELECT  step_name, message
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
        AND run_status <> 1 -- success

IF      @@ROWCOUNT <> 0
BEGIN
        RAISERROR('*** SQL Agent Job Prior Step Failure Occurred ***', 16, 1)

DECLARE @job_name NVARCHAR(256) = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = $(ESCAPE_SQUOTE(JOBID)))
DECLARE @email_profile NVARCHAR(256) = 'SQLServer Alerts'
DECLARE @emailrecipients NVARCHAR(500) = '[email protected]'
DECLARE @subject NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report: ' + @@SERVERNAME
DECLARE @msgbodynontable NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report For: "' + @job_name + '"'

--Dump report data to a temp table to be put into XML formatted HTML table to email out
SELECT sjh.[server]
    ,sj.NAME
    ,sjh.step_id
    ,sjh.[message]
    ,sjh.run_date
    ,sjh.run_time
INTO #TempJobFailRpt
FROM msdb..sysjobhistory sjh
INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id)
WHERE run_date = convert(INT, convert(VARCHAR(8), getdate(), 112))
    AND run_status != 4 -- Do not show status of 4 meaning in progress steps
    AND run_status != 1 -- Do not show status of 1 meaning success
    AND NAME = @job_name
ORDER BY run_date

IF EXISTS (
        SELECT *
        FROM #TempJobFailRpt
        )
BEGIN

-----Build report to HTML formatted email using FOR XML PATH
DECLARE @tableHTML NVARCHAR(MAX) = '
<html>
<body>
    <H1>' + @msgbodynontable + '</H1>
        <table border="1" style=
        "background-color: #C0C0C0; border-collapse: collapse">
        <caption style="font-weight: bold">
            ****** 
            Failure occurred in the SQL Agent job named: ''' + @job_name + ''' in at least one of the steps. 
            Below is the job failure history detail for ALL runs of this job today without needing to connect to SSMS to check.
            ******
        </caption>

<tr>
    <th style="width:25%; text-decoration: underline">SQL Instance</th>
    <th style="text-decoration: underline">Job Name</th>
    <th style="text-decoration: underline">Step</th>
    <th style="text-decoration: underline">Message Text</th>
    <th style="text-decoration: underline">Job Run Date</th>
    <th style="text-decoration: underline">Job Run Time</th>
</tr>' + CAST((
            SELECT td = [server]
                ,''
                ,td = NAME
                ,''
                ,td = step_id
                ,''
                ,td = [message]
                ,''
                ,td = run_date
                ,''
                ,td = run_time
            FROM #TempJobFailRpt a
            ORDER BY run_date
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile
    ,@recipients = @emailrecipients
    ,@subject = @subject
    ,@body = @tableHTML
    ,@body_format = 'HTML'

--Drop Temp table
    DROP TABLE #TempJobFailRpt
END
ELSE
BEGIN
    PRINT '*** No Records Generated ***' 
    DROP TABLE #TempJobFailRpt
END
END

Answer 4

Yet another answer to this really old question, this stored procedure seems to work very well.

https://karaszi.com/mailafterjob-send-email-after-agent-job-include-ouput-files

Tibor has included a great summary of the stored procedure, description of parameters and example.