GUID mismatch doesn't allow ALTER DATABASE

[RRinTetons]

I need to run an ALTER DATABASE statement against [msdb] to enable Service Broker. When I do that I get:

Msg 9776, Level 16, State 1, Line 1Cannot 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 1ALTER DATABASE statement failed.

I don't know what that means, but I don't like it!

Help?

-
Richard Ray
Jackson Hole Mountain Resort

 

[SQLBill]

This question was also asked in thread962-1668015. I've responded there.

-SQLBill

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

 

[RRinTetons]

The steps that finally worked looked like:

Stop the SQL Agent service

run

USE master
GO
ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [msdb] SET ENABLE_BROKER;
GO

Start the Agent serevicce and configure Database Mail.

In this case I got an error with existing Mail profiles/accounts (msdb was recovered from another server's backups after a crash). The Mail log complained about 'Object reference not set to and instance of the object'. Deleting and recreating the mail profiles and accounts identically fixed that and all dependent jobs now run correctly.

-
Richard Ray
Jackson Hole Mountain Resort