SQL Server Tips

Database mail setup for SQL Server Job Failure notifications

This will be another post about SQL Server database administration. This time I will post about Database Mail setup on SQL Server database. This task was the one I always doing as Database Administrator to give me notification when some SQL Server agent job was failed. First thing we need to prepare before configure this Database Mail, was our mail server configuration, like the user name, password, email server address, port number, etc. After everything prepared, here we go to setup Database Mail 🙂

Database Mail Setup

Open SQL Server Management Studio and connect to Database Server Engine,  from Object Explorer expand instance name, expand Management, right click on Database Mail and then click on Configure Database Mail.

database-mail-menu

Database Mail configuration welcome screen will appear, click Next

welcome-screen

 On the next screen, Leave the default options Set up Database Mail by performing the following tasks and then click Next

config-task-selection

For the first time Database mail configuration, you will get following warning popup. Click Yes and then click Next

first-time-popup

On New Profile page, type your email profile then click Add to add an email account

new-profile

On New Database Mail Account fill the correct information for all field and then click OK

new-db-mail-account

Back on the previous page, click Next

back-to-new-profile

On Profile Security page, check the option below Public column to make this new mail profile as Public Profile and also set this profile as Default Profile by click drop down menu below Public Profile column and select Yes and then click Next

profile-security

On Configure System Parameters page, leave all default options and then click Next

config-system-parameters

Verify all action the wizard about to perform, then click Finish to execute this action

complete-the-wizard

When you see all action was complete and its status was Success click OK to close this wizard.

finish-configure

Test Database Mail Configuration

We have to test if Database Mail was successfully configured by right click on Database Mail and click Send Test E-Mail and you will get the following page. Type email address that will used for testing and then click Send Test E-Mail.

test-email-dialog

On the screen below, click OK to process send this email

process-send

Go to your email and check if this email has arrived. If the email doesn’t arrive, check your email server  to make sure that you have properly configured your Database Mail.

Setup SQL Agent Job to Send Email Notification When Job Failed

From SSMS Object Explorer, right click on SQL Server Agent and click Properties

sql-server-agent-properties

On SQL Server Agent Properties, Click on Alert System under Select a page section, under Mail Session, check on Enable mail profile, then select the correct mail profile from Mail Profile drop down and then click OK

change-alert-system

We have to create an operator to receive email notification when SQL Server Agent job has failed, by expand SQL Server Agent, right click on Operators and click New Operator

operator-menu

On New Operator page, type operator name and recipient email address on Email Name field. You can type more than one operator by type every recipient email addresses separate by semi-colon ‘;’ and then click OK

new-operator

Expand Jobs folder under SQL Server Agent, right click on job name and click Properties

job-name-menu

On Job Properties page, click Notification page under Select a page, then mark the check box beside Email and from drop down menu, select correct operator name you’ve just createdFor testing purposes, you can change the option on the to When job completes and then click OK

job-properties

When the job fails, you should receive an email similar to the following email.

similar-email

That’s it. Database mail has configured properly. Now you don’t have to check every time to your Database Server if all job failed or not.

Advertisements

One thought on “Database mail setup for SQL Server Job Failure notifications

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s