HOW TO SET UP DATABASE MAIL FOR SQL SERVER JOB FAILURES

Hi Everyone!! There is one task I configure quite often as a Database Administrator… setting up Database Mail to send an email if a SQL Server job fails. It isn’t difficult, but if you miss one step, it won’t work. Ugh!!

SET UP DATABASE MAIL FOR SQL SERVER JOB FAILURES

I’ll list all the steps here so it can be done quickly and easily without losing all but one strand of hair on your head.

Here are the steps:

1. Configure Database Mail.

2. Create a SQL Server job.

3. Adjust the properties within the SQL Server Agent.

4. Create an Operator.

5. Adjust the SQL Server job to send on Failure.

1. Configuring Database Mail

To complete this step correctly, a properly configured mail server is needed. In most cases it is okay to use localhost, but that requires the installation of Microsoft IIS/SMTP. If your administrator will not allow the install of IIS on a server running SQL Server, get the name of a mail server that can be used

Step 1:

Open SQL Server Management Studio (SSMS), connect to the Database Engine, expand Management, right-click on Database Mail and then click Configure Database Mail.

Step. 2
You will then see the splash screen below. Click Next.

Step. 3
Leave the default option of “Set up Database Mail by performing the following tasks” selected. Click Next.

Step. 4
You will get a pop-up below if mail hasn’t been configured yet. Click Yes and then click Next.

Step. 5
Give the New Profile a name and then click Add.

Step.6
Fill in the correct information as show below.

· Email address– This should be the sender email address.

· Display name– This should be the name displayed when the email arrives.

· Reply email– This should be set properly too.

· Server name– The mail server address. In this case, I am using localhost.

· SMTP Authentication– I leave this section set to the default, but you can specify it to your needs if it is required.

Note that the OK button will remain grayed out until all required fields are completed. Once all the fields are completed, click OK.

Step. 7
Click Next.

Step. 8
Check the option to make the new profile Public. If the profile isn’t set to Public, permissions will need to be assigned properly. I am not going to discuss this here, but it will be important to note the difference

Step. 9
Review the System Parameters. I always use the default settings, but they can be adjusted as needed. You can review the settings in depth here. Click Next.

Step. 10
Click Finish.

Step. 11
You should see that each Action was completed and has a Status of Success. Click Close

Now let’s test.

Right-click Database Mail and select Send Test E-Mail… type in an email address to use for testing and click Send Test E-Mail.

You will then see the screen below.

If the email doesn’t arrive, check the mail server to make sure it is properly configured and will accept mail from the SQL Server. You can do this by sending an email via telnet.

2. Create SQL Server Job

At this point we know the SQL Server can send email. Now we need to setup a SQL Job and then set SQL so it sends emails if the job fails. The easiest way to test this is to setup a Backup job and set it to notify on “Success”. Once it is working properly, change the job to notify on “Failure”. Note: The maintainance plan widzard can also be used to setup a backup job very quickly.

3. Adjust the properties within the SQL Server Agent.

Right-click SQL Server Agent and select Properties.

Click on Alert System under Select a page.

· Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profileis selected.

· Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

Create an Operator

Under the SQL Server Agent, right-click Operators and select New Operator…

Type in the recipient email address in the E-mail name and click OK

Adjust the SQL Server job to send the email on Success (for testing purposes only).

Expand SQL Server Agent and click on Jobs. Find the job you created above, right click it and select Properties.

Under Select a page, select Notifications. Enable the first option, E-mail. Then select the Operator we just created and change the job to email When the job succeeds (for testing purposes only). Click OK.

That’s it!! Now we test. Run the job we create that will backup databases. You should receive an email similar to the one below once it completes:

If an email isn’t received, restart the SQL Server Agent once more and then test again.

Early History

Nulla feugiat lacus in vehicula lobortis. Sed feugiat lectus vitae arcu iaculis, sit amet pellentesque felis vestibulum. Nullam ut pulvinar elit, sit amet consectetur augue. Nulla eget dapibus diam. Donec arcu libero, elementum vitae laoreet in, luctus vel metus. Aenean porta odio at massa varius aliquam. Nunc iaculis magna diam, sed pharetra magna viverra at. Donec sapien magna, pretium in odio ut, congue gravida justo. Aenean mattis augue in sapien iaculis, non maximus tellus efficitur. Nam ut fermentum vivamus pharetra, elementum vitae laoreet in, luctus vel metus. Aenean porta odio at massa varius aliquam. Nunc iaculis magna.

Lorem ipsum dolor sit amet.
Nam liber tempor cum soluta nobis eleifend.
Typi non habent claritatem insitam; est usus.
Investigationes demonstraverunt lectores.

Map

Statistics

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce et fermentum ligula. Donec ultrices eu lorem bibendum suscipit. Aenean vehicula turpis pulvinar, hendrerit augue ac, iaculis velit. Integer ornare eget mauris sed pellentesque. Pellentesque quis odio sit amet elit consequat blandit. Sed vulputate euismod lobortis. Vivamus hendrerit ornare quam, nec viverra elit efficitur et lectus vitae arcu iaculis, sit amet pellentesque felis vestibulum. Nullam ut pulvinar elit, sit amet consectetur augue. Nulla feugiat lacus in vehicula lobortis. Sed feugiat lectus vitae arcu iaculis, sit amet pellentesque felis vestibulum. Nullam ut pulvinar elit, sit amet consectetur augue. Nulla eget dapibus diam. Donec arcu libero, elementum vitae laoreet in, luctus vel metus. Aenean porta odio at massa varius aliquam. Nunc iaculis magna diam, sed pharetra magna viverra at. Donec sapien magna, pretium in odio ut, ~~congue gravida justo. Aenean mattis~~ augue in sapien iaculis, non maximus tellus efficitur. Nam ut fermentum ex. Vivamus pharetra felis in molestie fermentum., lectus vitae arcu iaculis, sit amet pellentesque felis vestibulum. Nullam ut pulvinar elit, sit amet consectetur augue.

Donec aliquam auctor tortor, non malesuada magna consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra, vulputate turpis tincidunt, tempor lacus. Mauris euismod metus finibus sagittis tincidunt. Donec a augue viverra, porta metus non, congue metus. Nulla tincidunt purus non pharetra elementum. Aliquam fringilla elit ac felis consequat, ut viverra urna porta. Nunc malesuada quam risus. Mauris pellentesque pellentesque porta. Vivamus euismod vulputate tortor. Aenean quis pellentesque nibh consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra.

Nam et tellus eget orci interdum pulvinar at vitae ipsum. Nulla posuere sodales dapibus. Vivamus eleifend efficitur enim, ultrices semper libero. Suspendisse id faucibus massa. Mauris nec blandit leo. Vestibulum pulvinar, felis eget ullamcorper molestie, massa erat commodo turpis, nec vulputate ipsum risus eget ligula. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Nulla gravida, mauris eu blandit aliquam, dui mi rhoncus eros, id aliquet mauris lacus id enim. Nunc luctus ac ligula quis pharetra. Aliquam erat volutpat nullam venenatis luctus.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce et fermentum ligula. Donec ultrices eu lorem bibendum suscipit. Aenean vehicula turpis pulvinar, hendrerit augue ac, iaculis velit. Integer ornare eget mauris sed pellentesque. Pellentesque quis odio sit amet elit consequat blandit. Sed vulputate euismod lobortis. Vivamus hendrerit ornare quam, nec viverra elit efficitur et lectus vitae arcu iaculis, sit amet pellentesque felis vestibulum. Nullam ut pulvinar elit, sit amet consectetur augue. Nulla feugiat lacus in vehicula lobortis. Sed feugiat lectus vitae arcu iaculis, sit amet pellentesque felis vestibulum. Nullam ut pulvinar elit, sit amet consectetur augue. Nulla eget dapibus diam. Donec arcu libero, elementum vitae laoreet in, luctus vel metus. Aenean porta odio at massa varius aliquam. Nunc iaculis magna diam, sed pharetra magna viverra at. Donec sapien magna, pretium in odio ut, congue gravida justo. Aenean mattis augue in sapien iaculis, non maximus tellus efficitur. Nam ut fermentum ex. Vivamus pharetra felis in molestie fermentum., lectus vitae arcu iaculis, sit amet pellentesque felis vestibulum. Nullam ut pulvinar elit, sit amet augue. [9]

Lorem ipsum dolor sit amet.
Nam liber tempor cum soluta nobis eleifend.
Typi non habent claritatem insitam; est usus.
Investigationes demonstraverunt lectores.

Lorem ipsum dolor sit amet.
Nam liber tempor cum soluta nobis eleifend.
Typi non habent claritatem insitam; est usus.
Investigationes demonstraverunt lectores.

Lorem ipsum dolor sit amet.
Nam liber tempor cum soluta nobis eleifend.
Typi non habent claritatem insitam; est usus.
Investigationes demonstraverunt lectores.

Donec aliquam auctor tortor, non malesuada magna consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra, vulputate turpis tincidunt, tempor lacus. Mauris euismod metus finibus sagittis tincidunt. Donec a augue viverra, porta metus non, congue metus. Nulla tincidunt purus non pharetra elementum. Aliquam fringilla elit ac felis consequat, ut viverra urna porta. Nunc malesuada quam risus. Mauris pellentesque pellentesque porta. Vivamus euismod vulputate tortor. Aenean quis pellentesque nibh consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra.

Gallery

List

Donec aliquam auctor tortor, non malesuada magna consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra, vulputate turpis tincidunt, tempor lacus. Mauris euismod metus finibus sagittis tincidunt. Donec a augue viverra, porta metus non, congue metus. Nulla tincidunt purus non pharetra elementum. Aliquam fringilla elit ac felis consequat, ut viverra urna porta. Nunc malesuada quam risus. Mauris pellentesque pellentesque porta. Vivamus euismod vulputate tortor. Aenean quis pellentesque nibh consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra.

Lorem ipsum dolor sit amet.
Nam liber tempor cum soluta nobis eleifend.
Typi non habent claritatem insitam; est usus.
Investigationes demonstraverunt lectores.

Lorem ipsum dolor sit amet.
Nam liber tempor cum soluta nobis eleifend.
Typi non habent claritatem insitam; est usus.
Investigationes demonstraverunt lectores.

Lorem ipsum dolor sit amet.
Nam liber tempor cum soluta nobis eleifend.
Typi non habent claritatem insitam; est usus.
Investigationes demonstraverunt lectores.

Name	ISO 639	Classification	L1	Official Status (National)
Azerbaijani	az	Turkic, Oghuz	500,000	Azerbaijan
Bulgarian	bg	Indo-European, Slavic, South, Eastern	7,800,000	Bulgaria
Dutch	nl	Indo-European, Germanic, West	22,000,000	Belgium, Netherlands
English	en	Indo-European, Germanic, West, Anglo-Frisian	60,000,000	Ireland, Malta, United Kingdom
Finnish	fi	Uralic, Finno-Ugric, Finnic	5,400,000	Finland
Italian	it	Indo-European, Romance, Italo-Dalmatian	65,000,000	Italy, San Marino, Switzerland, Vatican City
Portuguese	pt	Indo-European, Romance, Western, West Iberian	10,000,000	Portugal
Turkish	tr	Turkic, Oghuz	12,000,000	Turkey, Northern Cyprus

References

Donec aliquam auctor tortor, non malesuada magna consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra, vulputate turpis tincidunt, tempor lacus. Mauris euismod metus finibus sagittis tincidunt. Donec a augue viverra, porta metus non, congue metus. Nulla tincidunt purus non pharetra elementum. Aliquam fringilla elit ac felis consequat, ut viverra urna porta. Nunc malesuada quam risus. Mauris pellentesque pellentesque porta. Vivamus euismod vulputate tortor. Aenean quis pellentesque nibh consectetur vitae. Nam condimentum justo metus, ac varius neque molestie eget. Aliquam ultricies eros viverra.