Whenever a new / existing job gets failed on an instance of SQL Server, we should recieve an email about failure notification. Please help on how we can automate it?
I'd suggest checking out this post from Greg Larsen - Automating SQL Server Agent Notification. You could manually create a SQL Agent job that runs periodically and searches for enabled jobs that don't have any notifications defined.
Here's an example of a dynamic SQL query you could put in a SQL Agent job to find SQL agent jobs without notifications and dynamically update them to have notifications. Make sure you uncomment the
EXEC when you put it in a SQL Agent job. You will also have to modify the variable
@Operator to specify the operator you want notified.
USE [msdb] GO SET NOCOUNT ON DECLARE @ExecStmt VARCHAR(4000) DECLARE @Operator VARCHAR(50) = 'DBA' -- place your operator name here SELECT @ExecStmt = STUFF(( SELECT ';' + 'EXEC sp_update_job @job_name = ''' + j.[name] + ''', @notify_email_operator_name = ''' + @Operator + ''', @notify_level_email = 2' -- 1=On Success, 2=On Faulure,3=always FROM dbo.sysjobs j WHERE j.enabled = 1 AND j.notify_level_email <> 1 FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') PRINT @ExecStmt --exec (@ExecStmt) GO