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