–By Ginger Keys
Database Mail is a feature of SQL Server that sends email from the SQL Server database engine to SMTP servers. It can be used to notify administrators when events happen in SQL server, such as job failures, failover notifications, or any other event deemed important in your environment. You can also use database mail for emailing reports or results of queries.
The main components of Database Mail are 1) sp_send_dbmail, which is a system defined stored procedure located in the msdb database, 2) MSDB database, which holds all stored procs, tables, and roles related to sending mail, and 3) Service Broker, which submits messages to the mail engine.
If you have not already set up database mail in your instance, basic steps for setting up your database mail are as follows:
For really in-depth info on configuring database mail, click here: https://msdn.microsoft.com/en-us/library/hh245116.aspx
The Service Broker is part of the database engine, and provides messaging and queuing for SQL Server. Generally when a database is created, the Service Broker message delivery is active by default. However, if the Service Broker is not active, messages created by Database Mail will remain in the mail queue. When looking at your Database Mail log, you might see an error message such as this:
Executed as user: DOMAIN\UserName. Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery. [SQLSTATE 42000] (Error 14650). The step failed.
The following steps will resolve the above error message:
1 – Activate Service Broker
To find out if your Service Broker is activated in msdb, execute this statement:
SELECT name, is_broker_enabled
WHERE name = ‘msdb’
A value of ‘1’ means the broker is enabled… a ‘0’ means it is disabled. If the broker is disabled, run this statement:
ALTER DATABASE msdb SET ENABLE_BROKER
2 – Enable Database Mail on Server
Also, make sure database mail is enabled on the server level by executing this statement:
sp_configure ‘show advanced options’, 1 — so you can see all options to configure
RECONFIGURE — run this so the value is set
sp_configure ‘Database Mail XPs’,1
3 – Check Status of Mail Queue
Furthermore, your database mail may not be sending because Database Mail may be stopped in the msdb database. Execute the following statement to make sure the status is STARTED:
If the status is STOPPED, run the following statement:
EXEC msdb.dbo.sysmail_start_sp — Start the queue
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!