This came up because we had many sql agent jobs that DBA’s don’t want to be alerted to like sql agent jobs that run SSIS packages. Those alerts need to be sent to the database developers instead. We use Idera Diagnostic Manager to send failed job alerts among other things. In order to send DBA related jobs just to DBAs, we needed to identify those jobs, and then have an inclusion rule for all jobs that start with “DBOPS-” in the DM alert templates to send those to DBAs. Then send all other jobs via a new alert template to db devs by excluding all jobs that start with “DBOPS-”
I didn’t want to manually update job names, so created a script to update as many as I could at one time.
select name,
‘EXEC dbo.sp_update_job @job_name = ”’+ name +”” + ‘, @new_name = ”DBOPS-‘+ name +”” AS scripttorun
from msdb.dbo.sysjobs
where name like ‘database%’ or name like ‘audit%’
and name not like ‘dbops-%’
and enabled = 1