0

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?

  • Create an Operator, and configure "Alert System" in SQL Server Agent property and restart it (SQL Server Agent). Simple. – Rajesh Ranjan Aug 2 at 7:35
  • Have a look at this article: Configure a SQL Server Agent Operator – McNets Aug 2 at 7:40
  • @RajeshRanjan I am not talking about configuring alert for one job. and the setting you are talking about will only configure alerting system in Agent which is not my requirement. – sachin-SQLServernewbiee Aug 2 at 8:13
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.