Enable Service Broker for MSDB for mail won't complete

[RRinTetons]

SQL Server 2008 R2 64-bit

I need to enable Service Broker in MSDB in order to get Database Mail working. When I try it through SSMS as part of configuring Database Mail SSMS freezes and has to be forced to quit. When I try it via t-sql with

use master
go
ALTER DATABASE [msdb] SET ENABLE_BROKER;
go

it executes but doesn't complete in as long as 8 hours. I can cancel query execution and everything seems normal.

How can I start to determine what is preventing this from working?


-
Richard Ray
Jackson Hole Mountain Resort

 

[SQLBill]

From the Books OnLine (BOL):

Enabling SQL Server Service Broker in any database require a database lock. To enable Service Broker in the msdb database, first stop SQL Server Agent. Then, Service Broker can obtain the necessary lock.

I hope that helps...

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[RRinTetons]

Grump. I misread that. I restarted Agent instead of leaving it off...

Thanks!

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

OK, new error:

Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (C41CF7A0-7DB3-4875-9721-778142BBE86E) does not match the one in sys.databases (5FA86F33-4927-4453-A170-66EA4E671E84).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SQLBill]

Did you restore the msdb database from somewhere else?

check out this thread on Microsoft's site:

http://social.msdn.microsoft.com/Fo.../thread/b63785c6-6c1f-4c44-aae3-97b9cbefc047/

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875