The Ji Village News

季庄新闻–Haidong Ji’s Blog

A couple of weeks ago, I set up database mail on one server, and verified that it worked. A few days ago, my friend called because the mail was not working for him.

This is the message I got when ran sp_send_dbmail stored procedure:

Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 68
Service Broker message delivery is not enabled in this database.
Use the ALTER DATABASE statement to enable Service Broker message delivery.

I then tried to go through Database Mail Configuration Wizard from Sql Server Management Studio, and picked Manage Database Mail accounts and profiles. This is the message I got:

Database Mail depends on Service Broker. Service Broker is not active in msdb.
Do you want to activate Service Broker in msdb? If you do not activate Service Broker,
Database Mail will queue e-mail messages, but will not be able to deliver the messages.
------------------------------
BUTTONS:
Yes
No
------------------------------

I clicked Yes, but nothing happened and the process was hung.

So I decided to run the script directly instead:

ALTER DATABASE msdb SET ENABLE_BROKER

Once again, it is hung.

It turns out that enabling Service Broker in any database requires a database lock. So I killed all connections to msdb and was able to run the script successfully. Database mail started working afterwards.

You may wonder what caused it to fail in the first place. After talking to developers, I learned that they restored msdb from a dev box. They did that because it is easier for them to move jobs and SSIS packages. As a result, Service Broker was disabled somehow. I certainly don’t recommend that approach, because msdb is a system database. As such, it has a lot of system meta data in it. Restoring it from some other server is just not a good idea.