One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account.
Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the Database Mail Configuration Wizard, the sp_configure stored procedure, or by using the Surface Area Configuration facet of Policy-Based Management.
Below is the step by step procedure for Enabling and configuring Database Mail in SQL Server.
Step 1: Create Profile and Account
You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.
This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:
Step 2: Configure Email
After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:
sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs', 1 GO RECONFIGURE GO
After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:
USE msdb GO EXEC sp_send_dbmail @profile_name='Email Profile', @recipients='test@Example.com', @subject='Test message', @body='This is the body of the test message.'
Quick troubleshooting queries for Database Mail
Check to see if the service broker is enabled (should be 1):
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
Check to see if Database Mail is started in the msdb database:
…and start Database Mail if necessary:
Check the status of the mail queue:
sysmail_help_queue_sp @queue_type = 'Mail'
Check the Database Mail event logs:
SELECT * FROM sysmail_event_log
Check the mail queue for the status of all items (including sent mails):
SELECT * FROM sysmail_allitems