July 22, 2010 at 1:43 pm
I have multiple instances with jobs that do not contain Notifications when the jobs fail. I am looking to add Notifications to these jobs. But rather than just add a notification to each job, is there a way to set a general database alert that will execute when any job fails for any reason?
July 22, 2010 at 2:14 pm
Yes there is. I use a trigger on the msdb.dbo.sysjobhistory to look for non-zero return codes and e-mail from there.
USE [msdb]
GO
/****** Object: Trigger [dbo].[checkForJobFail] Script Date: 07/22/2010 16:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[checkForJobFail] ON [dbo].[sysjobhistory]
FOR INSERT
AS
declare @msg varchar(2048), @maillist varchar(256), @mailfrom varchar(256), @jobName sysname, @subject varchar(256), @failStepID int
if (select step_id from inserted) <> 0
return
else
begin
select @jobName = name
from inserted i join sysjobs j on i.job_id = j.job_id
if (select run_status from inserted) = 0 --the job failed
begin
select @subject = 'Job ''' + @jobName + ''' failed on ' + @@servername
select @msg = message from inserted
end
else
if (exists (select h.instance_id
from inserted i join sysjobhistory h on i.job_id = h.job_id
where i.run_date <= h.run_date
and i.run_time <= h.run_time
and h.run_status = 0
and h.step_id > 0))--any step in the job failed
begin
select @failStepID = min(h.instance_id)
from inserted i join sysjobhistory h on i.job_id = h.job_id
where i.run_date <= h.run_date
and i.run_time <= h.run_time
and h.run_status = 0
and h.step_id > 0
select @subject = 'Job ''' + @jobName + ''' completed with errors on ' + @@servername
select @msg = 'Step ' + cast(h.step_id as varchar(3)) + ' failed
' +h.message from inserted i
join sysjobhistory h on i.job_id = h.job_id
where h.instance_id = @failStepID
end
else return -- the job didn't fail and no steps failed
select @maillist = 'Who Get's the alert'
select @mailfrom = 'DBAlert@' + replace(@@servername, '\', '_') + '.domain'
exec msdb.dbo.sp_send_dbmail
@recipients = @maillist,
@importance = N'HIGH',
@subject = @subject,
@body = @msg,
@profile_name = 'dbmail profile name'
end
July 22, 2010 at 2:15 pm
Although I can't take credit for the code, I inherited it from a previous DBA.
July 22, 2010 at 2:21 pm
Thanks for the reply! This does work, but the problem that I have with this is that Triggers are sort of hidden and I would like to centralize all of my alerts, so they can be found and documented in one place. I am searching for a way to do it through an alert.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply