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:
- you have to add a new step every job you have, and
- you have to pray that no-one messes up the alert proc,
Has anyone come up with a better solution?