Overview 
Configure Database Mail
Sending a Test e-Mail
Send an Email Notification When SQL Job Fails
Enable mail Profile
Creating an Operator
e-Mail configuration for the Job
For Customized email body & Subject

Overview

This we use at Job Level, by implementing this we can get the email notifications when evet the job is failed.

Configure Database Mail

The best way to do this is to use the Database Mail Configuration Wizard.
Open SSMS, Expand Management and Right-click Database Mail -> Then click "Configure Database Mail"

Set up Database Mail:


Give the profile a name and then add an SMTP account.


Click on "Add" button  Please enter the respective Account Name, Email Address, Display Name & Server Name. Port No is most of the time 25






Sending a Test e-Mail 

One the above is sone you will get an email like below.

Send an Email Notification When SQL Job Fails

Enable mail Profile

Right-click on SQL Server Agent, select Properties and click Alert System.

  • Select the "Enable mail Profile" check box.
  • Select the Mail system as "Database Mail" from the drop down.
  • Select the database mail profile name.
  • Select the "Include body of e-mail in notification message" check box and click OK.

 

Creating an Operator

Now create an operator. An operator is whom you want to send an email notification. Expand the SQL Server Agent node, right-click on operator and click New Operator. Enter the operator name. Enter the E-mail name (if you want to send an email to multiple people then you can use ";" as the separator)

e-Mail configuration for the Job

Now create some test job to send the email to the operator when the job is fail. Right-click on the Jobs node and select New Job. Enter the name and click on Steps.

When the job is executed or fails, a message is now automatically sent to the recipients.
These queries may help you know about the email audit:
select * from msdb.dbo.sysmail_account
select * from msdb.dbo.sysmail_sentitems


For Customized email body & Subject

Create the second/Nth step like below for the jobs where we need the email notification

Below is the step for Customized email Body & Subject.

So, the email will be triggered only when the job is failed.

Code

DECLARE @SubjectVal NVARCHAR(1000);

DECLARE @BodyVal NVARCHAR(300);

DECLARE @JobName NVARCHAR(128);


select @JobName = name from msdb.dbo.sysjobs where job_id = $(ESCAPE_SQUOTE(JOBID));

Set @SubjectVal = 'Der SQL Server Job [' + @JobName +'] auf Server ['+ HOST_NAME() + '] ist am ' + CONVERT(varchar, getdate(), 104) fehlgeschlagen;

Set @BodyVal = 'Job Name: ' + @JobName + '.' + CHAR(13)+CHAR(10) + 'Auftragsstatus: Failed ' + CHAR(13)+CHAR(10) + 'Aufgerufen von: '+ SUSER_NAME() + CHAR(13)+CHAR(10) + '.';


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA e-Mail-Benachrichtigung von Lachen SR103-054',

@Empfänger = 'Venkat.Thogati@nexus-schweiz.ch;CHZH_MIS_Support@nexus-schweiz.ch',

@body = @BodyVal,

@Subjekt = @SubjektVal

GO