Database mail is great tool which allows you to send notification emails from your SQL Server. You can use to send notifications for things like backups, error, resource alerts etc…

Configuring Database Mail

Using the SSMS Wizard

1. Go to Management -> Right Click on ‘Database Mail’ and select ‘Configure Database Mail’
2. Select the first option ‘Setup Database Mail by performing the following tasks
3. Give the Profile a name. The profile is a container for the email accounts. You can have multiple email accounts for a profile.
4. Click on the Add button in the SMTP Accounts section
5. Enter the details for your email account. I used my gmail account. You might need to check with your email provider for the settings for your account
   
6. Click Ok. You can add multiple accounts if you want. It will try the email accounts by the priority that they are set to.
7. On the Security page you can set it as a public or private profile. Public means the profile can be used by all users of the database. I set it as Public.
8. On the System Parameter page you can configure some more advanced settings if you want. Things like retry attempts, max file size etc… I left it as the default
9. Click Next, then Finish and it should configure without any errors

Test your configuration by right clicking on Database Mail and selecting ‘Send a Test email’. Enter your email address to test. If it doesn’t work, see the Troubleshooting section

Using T-SQL:

–Add mail account

EXEC msdb.dbo.sysmail_add_account_sp

@account_name = ‘Gmail’,

@email_address = ‘keenaghan.sean@gmail.com‘,

@mailserver_name = ‘smtp.gmail.com’,

@port = 587,

@enable_ssl = 1,

@username = ‘keenaghan.sean@gmail.com‘,

@password = ‘******’

–Add mail profile, storing new ID in output parameters

DECLARE @profID int

EXEC msdb.dbo.sysmail_add_profile_sp

@profile_name = ‘SQL Emails’,

@description = ‘Profile used for administrative mail.’,

@profile_id = @profID OUTPUT

–Add mail account to profile, using @profID from above

EXEC msdb.dbo.sysmail_add_profileaccount_sp

@profile_id = @profID,

@account_name = ‘Gmail’,

@sequence_number = 1

–Test the configuration by sending an email

EXEC msdb.dbo.sp_send_dbmail

@profile_name=’SQL Emails’,

@recipients=’keenaghan.sean@gmail.com‘,

@subject=’Testing Emails’,

@body=’If you recieved this email, it worked!’

Troubleshooting Database Mail

To check the Database Mail logs using SSMS:

1. Go to SQL Server Agent -> Error Logs
2. Selected the required data range
3. Tick the ‘Database Mail’ checkbox in the ‘Select Logs’ section on the left

To check the Database Mail logs using T-SQL:
There is a system table which stores the Database mail logs. To view this table using T-SQL:

SELECT * FROM msdb.dbo.sysmail_log
GO