failed to test Database Mail SQL Server

Question

Dear expert,

I tried to test send email by Database Mail from SQL Server (Management-->Database Mail), when select Send tes-email menu the Error Dialog Box appear 'Service Broker message delivery is not enabled in this database........ '

but when I enable service broker with ALTER DATABASE it took very long time to process..uuhhh

I kindly need your helps for solve this problem

thanks very much for all your reply

Regards

Answer 1

but when I enable service broker with ALTER DATABASE it took very long time to process..uuhhh

Hello,

You need exclusive access to the database to alter this setting. So as long as someone else is connected to the database (even you in an other session), you can't alter the setting; that's why it takes so long, it waits to get exclusive lock on the db.

So ensure no one is connected to the database; e.g. alter the database to restricted access or single user mode before.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

I would agree with the reply here. You need an exclusive access on the database to ALTER this setting. If you are willing to take the risk of rolling back the user transactions running on this database when you ALTER this setting ( so that your ALTER command does not have to wait until all current transactions are done ), you have the option for running this command with a ROLLBACK IMMEDIATE

--  turn on service broker 

ALTER DATABASE sbq_demo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

--  turn off service broker 

ALTER DATABASE sbq_demo SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE;

-- WITH ROLLBACK IMMEDIATE is optional. If used, it will rollback & close every transaction currently
-- running in your database.

Use this with caution. you have been warned !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com