How to setup Database Mail

Expand Management and you'll find Database Mail. While the configurations of Database Mail are stored within msdb, SQL Server Agent caches profile info in memory, so it can still send emails if SQL Server is down.

Configure Database Mail

1. Right-click ‘Database Mail’ (under ‘Management’) and select Configure Database Mail
2. Follow instructions and setup a New Profile
3. Continue, selecting your new profile as the Default
4. Send test email:

USE msdb;
GO
EXEC sp_send_dbmail @profile_name='Profile_Name',
@recipients='youremail@Example.com',
@subject='Test message',
@body='This is the body of the test message.'

NB. When you want SQL Server Agent to send you Notifications (e.g. when a job fails) you need to add yourself as an Operator and restart the agent

You can track the delivery status of an individual message using the following views:

• dbo.sysmail_allitems
• dbo.sysmail_sentitems
• dbo.sysmail_unsentitems

SMTP Relay

Most SMTP servers are configured to deny all email relay. This means it will not forward any emails from another server. Servers that allow this are open to spamming from other servers. SQL Server Agent demands the SMTP server be open to email relay.