Configuring SQL Server Email Notifications for Job Status Alerts

Email Notifications can be configured for SQL Server Jobs for Complete (Success or Failure), Success and Failure states.

Step 1: Configuring Outgoing Email Account
1. Navigate to SSMS > Object Explorer > Connect to Database Engine > Root Node (server) > Management > Database Mail. First time, it may prompt as “Database Mail is not enabled, would you like to enable?” Accept and Proceed.
2. Right Click > Configure Database Mail
3. Provide the Database Mail Profile details
4. Add an Email Account to Profile you would like to send notifications from, choose Windows Authentication for SMTP Authentication
5. In Profile Security dialog, keep the profile “Public”. (For some reason, I was unable to configure notifications with “Private” profile.)

Step 2: Add Recipient Email Account (Operator)
1. Navigate to SQL Server Agent > Operators
2. Right Click > New Operator
3. Provide Display Name, Email Address (under Email Name) and configure the duty schedule. You can ignore two fields below Email Name.

Step 3: SQL Server Agent Alerts
1. Right Click SQL Server Agent > Properties > Alerts
2. Check the checkbox “Enable mail profile”. Choose Mail Profile.

Step 4: Configure Alerts for Job(s)
1. Navigate to Job Properties Dialog > Notifications.
2. Check “E-mail:”, choose the Operator, and choose the condition when you want the notification to be sent.
3. Click OK

References (Configuring in T-SQL)
1. http://technet.microsoft.com/en-us/library/ms187605(v=sql.105).aspx
2. http://msdn.microsoft.com/en-us/library/ms175962.aspx

 

Share This!

Leave a Reply

Your email address will not be published. Required fields are marked *