Setting Up Alerts for All SQL Server Agent Jobs

Some times clients ask to enable alerts for the sql agent jobs, it will be difficult when we have more jobs, because we need to goto each and every job and enable the notifications.

Here is the script for enabling for the jobs,

Steps :

1. Create Mail Profile

2. Enable the mail profile for agent (SQL Server Agent Properties –> Alert System –>select Enable Mail profile & Select profile)

3. Create Operator with Mail ID. (Ex : DBA)

4. Run below script for enabling the alerts for all the jobs.

We need to decide when we want email to be trigger (i.e  after Complete, Failed, Success) below table will give the details.

Value Description
0 Never
1 On Success
2 On Failure
3 Always

In the below script i assume that , Alert is sent on 2 (On Failure) and alert will be sent to DBA (Operator).

use msdb
GO

DECLARE @operator varchar(50)
SET @operator = ‘DBA’

SELECT ‘EXEC msdb.dbo.sp_update_job @job_ID = ”’ + convert(varchar(50),job_id) + ”’ ,@notify_level_email = 2, @notify_email_operator_name = ”’ + @operator + ”’ ;’ FROM sysjobs

 

Execute the script which is generated by above script.
 

 

 

 

 

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s