I was trying to find a alert which could fire immediately upon any of the job failure in the server to send an email. Unfortunately I couldn’t find any the alert related to this. So I thought of writing a DML trigger which should fire immediately when an entry is inserted in sysjobhistory table on msdb database. The next step is that the trigger should send a mail when there is failure in the job, hence Ive used run_status column in the table, as all the DBAs know when this column has a value 0 then it states the job has been failed. Ive tried this script in SQL Server greater than version 2000 however its not working on it. This script works only on SQL Server 2000, still some of them are still working on SQL 2000 so I thought this script has still value. This DML trigger will send an email upon any of the job failure.
- SQL Server 2000
In the script below make sure to change the mail address used in the recipients field. You also need SMTP mail to be configured in SQL Server to work however you can modify the script to include CDO messaging.
/* Written By : Vidhya Sagar www.sql-articles.com */ USE MSDB GO CREATE TRIGGER jobfailurenotify ON sysjobhistory FOR INSERT AS DECLARE @failed TINYINT, @subject VARCHAR(300), @body VARCHAR(500) SELECT @failed = COUNT(*) from inserted where run_status=0 IF @failed > 0 BEGIN SELECT @subject=name from msdb.dbo.sysjobs where job_id in (select distinct(job_id) from inserted where run_status=0) SET @subject=@subject + ' job failed on ' + CONVERT(VARCHAR(25), GETDATE()) SET @body=@subject + '. Please look in to this. Server Name : ' + CONVERT(VARCHAR(25),SERVERPROPERTY('SERVERNAME')) EXEC master.dbo.xp_sendmail @recipients = Nemail@example.com', @subject = @subject, @message = @body END ELSE BEGIN PRINT 'No job failures found' END
Sample Mail Output
Sent: Monday, March 02, 2009 6:50 PM
Subject: TESTING job failed on Mar 2 2009 6:50PM
TESTING job failed on Mar 2 2009 6:50PM. Please look in to this. Server Name : SAGARSYS
If you have any concerns or queries regarding this script. Just post it out in our Forums Section.