Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier