Sql-server – How to have SQL Server email me the error details when a job fails

monitoringsql server

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?

Best Answer

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.

Related Question