VSG's blog: #24.Troubleshooting SQL Server Agent Email Notification

Troubleshooting SQL Server Agent Email Notification:

Why?

For most of the important packages & Jobs we had scheduled to run in SQL we would like to get a notifications via email on its success or failure. This will help us to take necessary action if the job fails for some reason & on the other hand if the job succeeds we will get the confirmation that we are good for the day with that particular job. On various occasions though the mails are configured it may not reach your mail box. Let’s see how to troubleshoot those scenario’s & fix what we miss & generate emails to our mail box.

How?

I am going to rely on some of the SQL server stored procedure & system functions to accomplish our goal. I am going to create a TESTJOB in my SQL Server Agent, We will proceed step by step on receiving email Alerts. Before we start here are some terms that we should be aware of

Operators:

Operators are aliases for people or groups that can receive electronic notification when jobs have completed or alerts have been raised. The SQL Server Agent service supports the notification of administrators through operators. Operators enable notification and monitoring capabilities of SQL Server Agent. You may use Operators folder to create a new operator in SQL server agent or you may run below query to create an operator.

 EXEC dbo.sp_add_operator

    @name = N'TEST EMAIl OPERATOR',

    @enabled = 0,

    @email_address = N'vsrinivasagopalan@gmail.com',

    @pager_address = vsrinivasagopalan@gmail.com'',

    @weekday_pager_start_time = 090000,

    @weekday_pager_end_time = 180000

GO

Database Mail profile:

A Database Mail profile is an ordered collection of related Database Mail accounts. To send mail you must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile. Profiles allow database administrators to reconfigure stored procedures and database applications that use e-mail without changing the application code. For example, a profile can be configured with one set of e-mail accounts during application development and testing, and then be updated with a different set of accounts when the application moves to production. The application uses the same profile name, but sends e-mail using a different list of e-mail servers.

The following function will give you details of current mail profile on that SQL server.

EXEC msdb.dbo.sysmail_help_profile_sp

Let’s begin.

Scenario1: My Job completes successfully but no mails received to my Mailbox.

Here I had configured a simple job with email notifications configured. I am going to use an operator called TEST EMAIL OPERATOR to send email notifications. This Operator is associated with a mail profile called SQL Alerts. I had made a settings like on job success I should receive an email & I will get a page to my mailbox on job failure from the SQL server Agent of SQL2012.

I am going to run this job & will see what happens next. The job completed successfully but no emails triggered to our mail.

Troubleshooting Scenario 1:

Verifying SQL Server Agent Alert properties:

Ensuring whether the Alert system in SQL server agent got enabled in SQL Server Properties will be first step of troubleshooting. Check whether Mail profile is correct or not. Also the Operator used for sending Emails & page should be mentioned correctly. If you see anything not correct here fix the same, so that your database mail will work correctly.

Let’s have a look on Job history once as below. This shows the job ran & completed successfully. It indicates the job ran at 7/16/2015 9:46:18 PM.

We can check whether any emails have been triggered for this time using system table msdb..sysmail_allitems

Thus no Emails got triggered from SQL. Our next step is to check SQL operator used for sending emails.

On verifying the Email Operator property it shows this haven’t got enabled yet. I am going to enable this & will rerun TEST job.

I had received an email in my mailbox, thus enabling our TEST EMAIL OPERATOR had done the trick to send us email on the job success.

Scenario2: My Job Fails but no mails received to my Mailbox.

I am making some changes to my query so that TEST JOB fails now & we expect an email from SQL to be triggered to my mail box as we had enabled. Now let’s follow same process as above.

Troubleshooting Scenario 2:

On checking Job history you may identify something as below. The Operator is not currently on duty to send email.

On verifying the properties of TEST EMAIL OPERATOR again, we can see the pager on duty is not yet scheduled. We will schedule it now & also will change the time of workday begin & workday end.