Service Broker

Last Post 22 Feb 2011 09:27 AM by PaulMcKibben. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
PaulMcKibben
New Member
New Member

--
21 Feb 2011 08:13 AM
I am trying to enable service broker on the msdb database so that I can enable DB Mail on a SQL 2005 server. 

I am a system administrator on the server and a sysadmin in SQL Server.  On the server in question, I am a member of the public database role only on the msdb database.

I am trying to execute the following alter database msdb set enable_broker to enable to the service broker for the database.

The issue is that it has run for approximately 7 minutes without the query ending by itself which I believe is too long under normal conditions. 

Am I being impatient? 

Any help that can be given would be appreciated.

Thank you.
rm
New Member
New Member

--
21 Feb 2011 09:15 AM
It's blocked, have to stop sql agent to enable service broker in msdb.
PaulMcKibben
New Member
New Member

--
22 Feb 2011 08:03 AM
Thank you for your response.

After I stopped the Agent, I executed the alter statement and received following:

Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (46BA6922-0A7F-40EF-8201-16F0961A7B6C) does not match the one in sys.databases (5D5541A9-C405-49A2-B01D-08D199ADB116).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Any suggestions?
PaulMcKibben
New Member
New Member

--
22 Feb 2011 09:27 AM
I found and corrected the issue. Here is what I did.

1. Stop SQL Server Agent
2. ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE
3. ALTER DATABASE msdb SET ENABLE_BROKER
4. Restart SQL Server Agent

Step 2 updates the Service_broker_guid with a new guid.
Step 3 enables Service Broker.
You are not authorized to post a reply.

Acceptable Use Policy