Alter Database to enable Service Broker runs long

Question

I have run four of these alter database statments to enable service brokers and they have run about 1 hour now. I wonder if this is normal. Does it take long time to alter to enable service broker through a command? I am using 2008 SQL server enterprise evaluation edition on windows.

ALTER

 

DATABASE Content SET ENABLE_BROKER ;

GO

Thank you for your support

 

Answer 1

U have not used the no_wait hint

 

ALTER

 

DATABASE [DatabaseName] SET DISABLE_BROKER WITH NO_WAIT

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 2

Does your command mean 'ENABLE_BROKER'? I see 'DISABLE_BROKER.... Is that correct? Or do you mean I have to disable first and enable?

 

Do we have to enable SERVICE BROKER for any system databases? Or do I have to enable just user databases?

Answer 3

In a server there are 5 databases; 2 user and 3 system databases.

The service broker for the 2 user and tempdb databases are enabled and service broker for msdb and master are disabled ('FALSE' for IS_BROKEN column in sys.databases).

When I run a script to setup an email notification I keep getting this error, "Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery."

I wonder what for database I should enable service broker?

 

 

Answer 4

I'v tried to enable service broker for msdb database with this command but have difficult to do so. Below is the error I have gotten. What should I do to fix this and enable the service broker?

ALTER

 

DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (62198123-2367-48FB-AC38-CF4ECC63342A) does not match the one in sys.databases (00000000-0000-0000-0000-000000000000).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

Answer 5

Sorry , It should be enabled first.

ALTER

 

DATABASE [DatabaseName] SET ENABLE_BROKER WITH NO_WAIT

 

You dont have to enable service broker for system databases.

Why do want to enable service broker, are u going to develop something for  notification or query processing

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 6

Yes, I am trying to get email notification.

When I ran your command I got below message. It seems that someone is using msdb database.

Msg 5070, Level 16, State 2, Line 3

Database state cannot be changed while other users are using the database 'msdb'

Msg 5069, Level 16, State 1, Line 3

ALTER DATABASE statement failed.

Answer 7

When you try to enable SERVICE_BROKER for a database, it requires a database exclusive lock. That means no one should be connected to the database at that time. Usually this query runs in an instant if there are no conflicts in the locks but if there are any open or conflicting locks then the command will be blocked until it gets the exclusive lock.

You should run this command during a maintenance window if you can.

---

Sankar Reddy http://sankarreddy.com/

Answer 8

I'v tried to enable service broker for msdb database with this command but have difficult to do so. Below is the error I have gotten. What should I do to fix this and enable the service broker?

ALTER

 

DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (62198123-2367-48FB-AC38-CF4ECC63342A) does not match the one in sys.databases (00000000-0000-0000-0000-000000000000).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

First thing you have to do is Stop the SQL Agent Service. 

http://msdn.microsoft.com/en-us/library/ms166057(SQL.90).aspx

Once that has shut down, you should be able to enable Service Broker.  IF you get the same message you posted above about the Service Broker GUID mismatch you need to regenerate the Broker Guid for the database with:

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

 

 

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 9

Hi, Jonathan.

Thank you for your advice.

I stopped the agent and ran,

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

This command ran successfully.

After I ran your command and checked the value of 'is_broker_enabled' column for msdb it is still 'false' meaning it is not enabled.

I don't think the command you gave me will enable the service broker for msdb database. What command should I run after your command? Should I run the command while the agent is stopped or after I restart?

Answer 10

I ran the above command but it doesn't change service_broker_grid value in sys.database view. The value remains as '00000000-0000-0000-0000-000000000000'. I wonder how I can change this value.

Answer 11

Hi, Jonathan.

Thank you for your advice.

I stopped the agent and ran,

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

This command ran successfully.

After I ran your command and checked the value of 'is_broker_enabled' column for msdb it is still 'false' meaning it is not enabled.

I don't think the command you gave me will enable the service broker for msdb database. What command should I run after your command? Should I run the command while the agent is stopped or after I restart?

ALTER DATABASE msdb SET ENABLE_BROKER

The command I provided was to fix the mismatched GUID's which would prevent it from being enabled, you'd still have to enable it after running that.  Perhaps I should have been more detailed in my response.

 

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 12

At this point my question would be, what steps did you do to get msdb messed up?  Was there a backup restore, copy/paste MDF and LDF file from another server, or other operation that was done?  If SET NEW_BROKER didn't work, I am at a loss.  What is the compatibility level of msdb?  Did it come from a server at the same patch level as the one you are working with?  Was this an inplace upgrade?  Something is wrong with your configuration if this failed to fix it.  Now its a matter of figuring out what that is.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 13

I will try as you suggested and post the result.

Answer 14

Should I submit ALTER DATABASE msdb SET ENABLE_BROKER while SQL agent stopped or after enabling?

Answer 15

Should I submit ALTER DATABASE msdb SET ENABLE_BROKER while SQL agent stopped or after enabling?

It doesn't really matter if you use WITH ROLLBACK IMMEDIATE, but the preferred way would be with SQL Agent Stopped so that you aren't kicking its connections to make the change.  With the Agent Stopped, you should be able to issue the ALTER DATABASE commands without having to use WITH ROLLBACK IMMEDIATE.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 16

I stopped the SQL Agent and submitted

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

This command ran successfully.

Then I submitted ALTER DATABASE msdb SET ENABLE_BROKER and I got,

Msg 9776, Level 16, State 1, Line 1

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (62198123-2367-48FB-AC38-CF4ECC63342A) does not match the one in sys.databases (00000000-0000-0000-0000-000000000000).

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

 

You said,

"At this point my question would be, what steps did you do to get msdb messed up?  Was there a backup restore, copy/paste MDF and LDF file from another server, or other operation that was done?  If SET NEW_BROKER didn't work, I am at a loss.  What is the compatibility level of msdb?  Did it come from a server at the same patch level as the one you are working with?  Was this an inplace upgrade?  Something is wrong with your configuration if this failed to fix it.  Now its a matter of figuring out what that is."

 

I will investigate more according to your suggestion.

 

Thank you,

 

 

Answer 17

A server has 3 system databases and 2 user databases (Content and Glossary). I see that all these databases have sys.databases view. In those sys.databases I see the value of the column 'IS_BROKEN_ENABLED' for the system databses is FALSE meaing Service Broker disabled. But the column value for the 2 user databases is 'TRUE'.

I try to get email notification of the back up jobs for the the 2 user databases(Content and Glossary).

My question is 'should I enable Service Broker for 3 system databases(master, msdb and tempdb) also?

Answer 18

Both commands SET NEW_BROKER and DISABLED_BROKER for test purpose worked successfully but not ENABLE_BROKER command. I keep getting an error,

"Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (62198123-2367-48FB-AC38-CF4ECC63342A) does not match the one in sys.databases (00000000-0000-0000-0000-000000000000)."

At this point I feel giving up in desperation.

Answer 19

Have you tried running run ALTER DATABASE OperationsManager SET NEW_BROKER. Then running ALTER DATABASE OperationsManager SET ENABLE_BROKER.

Rory McCaw - OpsMgr MVP

Check out www.infrontconsulting.com/training.php for great System Center training for consultants with real world experience with the System Center applications.

---

Rory McCaw, MOM MVP [Infront Consulting Group]

Answer 20

Why would you reply to a post that is nearly a year old, not offer anything new to the thread, and then self propose your own response as the answer immediately?  This post never even mentions OperationsManager as a database name, it specifically relates to msdb.

---

Jonathan Kehayias | Senior Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Feel free to contact me through My Blog or Twitter
Please click the Mark as Answer button if a post solves your problem!