Yesterday I needed to enable database mail on my development server to work on another phase of my project. Open SSMS, expand the Management Tab, right click on Database Mail and click on Configure Database Mail. Easy peasy, right? Except it would complain that I needed Service Broker running, fair enough, but when it tried to start it my system went away.
Worse yet, nothing in any logs.
I got a partial clue when I found a Stack Overflow post titled Enable SQL Server Broker taking too long time. It had two helpful things:
SELECT name, is_broker_enabled FROM sys.databases
ALTER DATABASE [mydatabase] SET ENABLE_BROKER
From the first I learned that only TempDB was enabled. I used the second line of code to turn it on for my development database, but it didn’t work for my MSDB. A reply suggested adding WITH ROLLBACK IMMEDIATE to the ALTER. When I did that and tried to run it, I got something like this:
Alter failed for database 'xxx' Cannot enable the Service Broker in database "xxx" because the Service Broker GUID in the database (guid number) does not match the one in sys.databases (different guid number) ALTER DATABASE statement failed.
Not very useful, EXCEPT it steered me to a blog post by Grumpy DBA, in which he describes the problem as a mismatch in the service broker GUID coming from copying a database from another server installation. Fortunately he offered a solution:
ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE
Here we force a new GUID upon the database, the Rollback Immediate forces any open connections closed. Obviously you want to be VERY careful doing something like that in production, but you should be OK in development.
At least now I can get in to the database mail setup and get on with my project.
THANK YOU, GRUMPY DBA!