SQL Agent Job Notifications

If we have a mission critical application then all Agent jobs which support that application is also that much critical, if any SQL Job fails then immediately we need to notify to a particular person or a group. Using SQL Server Operators we can achieve this.

Below is the procedure

Initially we need to configure database mail to send notifications from SQL Agent Jobs.
Refer - http://calyansql.blogspot.in/2012/10/configure-database-mail-sending-status.html

Steps
1. Configure Database Mail
2. SSMS –SQL Server Agent – Operators – Right Click – New Operator
Name – Notification Operator
Email name – test@yahoo.com
Pager e-mail name – test@yahoo.com
3. Under Jobs – Select job for which you want to configure notifications
4. Job – Properties – Notifications
 – Check Email – Select Operator from dropdown list and condition
 – Check Email – Select Operator from dropdown list and condition
5. SQL Server Agent – Properties
6. Select Alert System – Check Enable mail profile -- choose appropriate profile name from the mail profile dropdown list
7. Check Enable fail-safe operator – select operator name and check Email, Pager under Notify using section.
8. Click OK