Job failure E-Mail Notification Automation

Configuring the notification for Job failure alerts.

 Assumption – we assume that Database Mail is configured. If not familiar with the DB mail procedure. In the beginning of the article its given. Please refer the below link.

http://www.orcsweb.com/blog/desiree/how-to-set-up-database-mail-for-sql-server-job-failures/

Follow the below steps for configuring notification on job failures.

documented in link – Notification_Job_failures_Doc1

Step1) Click on Alert System under Select a page.

  • Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.
  • Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

Step2) make sure the operator is created with email id. for our convenience, we have created a alert by name – DBA_Alert


/****** Object:  Operator [DBA_Alert]    Script Date: 06/20/2015 11:32:15 ******/

EXEC msdb.dbo.sp_add_operator @name=N'DBA_Alert',/* can give any name*/
@enabled=1, @weekday_pager_start_time=90000, @weekday_pager_end_time=180000,
@saturday_pager_start_time=90000, @saturday_pager_end_time=180000,
@sunday_pager_start_time=90000, @sunday_pager_end_time=180000,
@pager_days=0,@email_address=N'[Specify_an_email_id]',
@category_name=N'[Uncategorized]'

GO

Step3) exec script to check job name & job_id which is required to change the notification. 


select name,job_id from msdb.dbo.[sysjobs]
where name like '%[job_name]%'

Step 4) execute the below query with job_id fetched from above query. 


EXEC msdb.dbo.sp_update_job @job_id=N'',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'DBA_Alert';

Note – if you are trying to update job failure notification for more than 1 job. You can use Excel for automating the script as shown below.

MSEXCEL-Job_failure_notification_script_automation_step

Excel Formula used at query column :


="EXEC msdb.dbo.sp_update_job @job_id=N'"&column number of Job_id&"',
@notify_level_email=2, @notify_level_netsend=2,
@notify_level_page=2, @notify_email_operator_name=N'DBA_Alert';"

Ref –

Excel formula used – http://chandoo.org/wp/2008/09/22/sql-insert-update-statements-from-csv-files/

email notification steps –http://www.orcsweb.com/blog/desiree/how-to-set-up-database-mail-for-sql-server-job-failures/

Advertisements
This entry was posted in MSSQL. Bookmark the permalink.

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s