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.
11 responses to “Service broker and database mail”
Your blog was the only one I found with this problem.
I killed all msdb connections and it stopped hanging 🙂
Thanks.. this was useful for me
You are welcome guys. Glad it helped!
This was very useful, excellent advice. MSDN should have this in their documentation!
Great post and find. This was very useful. Just what the SQL Doctor ordered.
Glad it helped Russell.
The first thing to check is whether SQL agent has started. If so stop it and check using sp_who2 to see anything connection to msdb exists.
Right on, Edwin! Cheers.
Thanks a lot.
Nonno, don’t need to kill anything in msdb, just stop the SQL agent.
It is most likely that the SQL agent process the msdb, then enable it using wizard or Alter command.