Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72

Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.

To check Service Broker is enabled on MSDB, please execute the given query:

select is_broker_enabled,* from sys.databases where name = MSDB

If the output value of is_broker_enabled is 0 ->

Service Broker NOT enabled

Note: A value of 0 indicates that Service Broker message delivery is not activated in the MSDB database

If the output value of is_broker_enabled is 1 ->

Service Broker IS enabled

To enable the Service Broker on MSDB database is as follows:

3) Run the query ALTER DATABASE MSDB SET ENABLE_BROKER

4) Check the service broker is enabled or not by executing the below query:

4.1) select is_broker_enabled from sys.databases where name = MSDB

4.2) The output value 1 means service broker is successfully enabled

Reason to stop SQL Agent services: Enabling Service Broker is not allowed when the database is in USE. Obviously SQL Agent will use MSDB database.

After the execution of the above, please exeute the below scripts to confirm that we are receving mails:

Thank you. Even if you stop SQL Agent it is possible that another call may be even if sleeping , need to be terminated forst.

But that thanks for this. Why does a restore a DB causes the Service Broker enabled to go off ?

Can we use the same method in cluster environmnet also?

This will also work: alter database msdb set enable_broker with rollback immediate;

I Love my work that what I m doing. I have good experience in SQL Server Database Administration on Production environment. I will work in cool manner even in under pressure OR critical servers down because if you are in tense at that moment everything will be blank in our mind. So I will suggest same thing to all. Still a lot to learn to achiev more & more. Sometimes i will learn new things from my any one (either Jr or Sr)… 🙂

Query to pull the users created in a database