Database Mail Error – SQL Server

–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:

1. Get SMTP mail server information (name, port)
2. Create an Operator (in SQL Agent)
3. Open the Configure Database Mail wizard (located in the Management folder of SSMS)
4. Create a Profile
5. Create an Account (email address, display name, reply email, SMTP info)
6. Enable Database Mail in SQL Agent Properties
7. Send Test email

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:

Message

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:

USE master

GO

SELECT name, is_broker_enabled

FROM sys.databases

WHERE name = ‘msdb’

GO

A value of ‘1’ means the broker is enabled… a ‘0’ means it is disabled. If the broker is disabled, run this statement:

USE [master]

GO

ALTER DATABASE msdb SET ENABLE_BROKER

GO

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

GO

RECONFIGURE — run this so the value is set

GO

sp_configure ‘Database Mail XPs’,1

GO

RECONFIGURE

GO

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:

USE msdb

GO

EXECUTE dbo.sysmail_help_status_sp;

 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!