Email Notification On SQL Job Failure

Introduction

In this article, we will discuss how to send email notifications when our SQL job fails. The purpose to configure is to intimate the user to take care of failed jobs. We will cover how to configure Database Mail, Email profile, SMTP Configuration, Operator to send email, notification, and SQL Server Agent configuration.

Background

We know to perform a series of actions automatically on SQL server side, we need to configure jobs. A task that is configured will be run one or more times and monitored for success or failure. Some examples of SQL jobs are daily, weekly, monthly and quarterly reports, sending auto emails, newsletters, writing log files, and audit logs to be sent automatically on local or remote locations.

Why Configure Email Configuration

When our job is running and we can face any issue while executing the script. Such issues occurred when working with files, simultaneously data insertion, working with SSDT (SQL Server Data Tools), and many more scenarios. SQL job get stuck and take more time as expected and finally fails. On job failure, we need to know on an immediate basis to get rid of the problem.

Steps to Configure Email Notification on Failure

These are steps to be performed to configure email notification on SQL job failure:

Step 1

Under Management, Configure Database Main. Right click on Database Mail and select the first option (Configure Database Mail)

Step 2

We are creating a new profile, so select the first option and click on Next.

Step 3

Write the profile name and description then click on Add button to add SMTP settings.

Step 4

I am using Gmail SMTP configuration; you can use according to your settings. From email address is used to send email notifications.

Step 5

When profile is ready then click Next to proceed.

Step 6

Configure your system level parameters like retry mechanism, attached files size, and allowed/prohibited extensions. Click on Next. On next screen shows all the details of your account simply select Finish. Your all configuration is successfully built.

Step 7

Create an Operator

Now we have to make an operator to whom you want to send email when the SQL job is failed

Expand SQL Server agent

Right-click on Operators and select new operator

Step 8

In the following screen enter your Operator name and E-mail name to whom you want to send emails. If you want to send email to more than one person just separate the email address with ';'

After that click OK and operator is ready to send emails.

Step 9

Create a New SQL Job

Step 10

Select Notifications and check on email and select operator which we define previously.

It helps to send email when the jobs fail. After that click on OK.

Summary

This article shows the step-by-step configuration of email notification when SQL job fails. These notifications are very necessary to take immediate necessary actions. Happy Configuration!!