Automations

How To Modify Job Notification

I was thinking perhaps someone would want to rename their Job Alert notifications; if they followed the instructions I put on this blog. Don’t forget you have to modify the Trigger on the msdb..sysjobhistory table as well. Additionally; you would need to deploy the changes across all your database servers to make it consistent. Here’s some SQL logic to take care of all of that for you. It will both check for the Job names to change, and confirm they were indeed changed along with the create date of the Trigger after it’s dropped and recreated with the appropriate Job name coded within it.

use msdb;
set nocount on

if exists(select name from msdb..sysjobs where name = 'SEND SQL BACKUP ALERTS') begin
exec msdb..sp_update_job
@job_name = 'SEND SQL BACKUP ALERTS'
,	@new_name = 'DATABASE ALERTING - Backups'
end

if exists(select name from msdb..sysjobs where name = 'SEND SQL JOB ALERTS') begin
exec msdb..sp_update_job
@job_name = 'SEND SQL JOB ALERTS'
,	@new_name = 'DATABASE ALERTING - Jobs'
end

use msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

drop trigger trig_check_for_job_failure
go

create trigger [dbo].[trig_check_for_job_failure] on [dbo].[sysjobhistory] after insert
as
begin
set nocount on
declare	@is_fail	int
set	@is_fail	= (select case when [message] like '%The step failed%' then 1 else 0 end from msdb..sysjobhistory where instance_id in (select max(instance_id) from [msdb]..[sysjobhistory])) if	@is_fail	= 1
begin
exec msdb.dbo.sp_start_job @job_name = 'DATABASE ALERTING - Jobs' end
end
go

select name from msdb..sysjobs where name like 'Database%' order by name asc

select
left(strig.create_date, 19) + ' ' + datename(dw, strig.create_date) ,	'table_name'	= stable.name
,	'trigger_name'	= strig.name
from
sys.triggers strig join sys.tables stable on strig.parent_id = stable.object_id where
stable.name = 'sysjobhistory'
order by
stable.name, strig.name asc
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s