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).