Sometimes you can get a problem when trying to configure Service Broker on a database that has been restored / copied from another source (e.g. to a DR server), or even when trying to enable Database Mail on an msdb database that has been copied from elsewhere.
The error is something along the lines of:
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.
This is because the master database also keeps track of service broker for each database, so this is just saying the master database thinks service broker has last processed a different record to the the one the database believes.
In the situation above, where we know this is the case because the database has come from another source, the quickest fix is just to reset the service broker as follow:
ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE
This will drop and recreate all service broker settings and queues - so please only use this if you really do want to reset everything, don't do it if you have queues with statements in that you want processing - but for a quick fix on a DR database it can save a lot of headscratching.
10 comments:
Hello. Do you know how to check for this condition proactively? I can look at sys.databases to get the service_broker_guid, but what do I compare that to? Thank you for the post.
Good Post.it solved my problem.
Thanks for the post buddy. It helped.
it worked for me too
Thanks Buddy.. You saved me from firing..
Thanks!!
thanks buddy ,it helped me.thanks again
Thank you,it solved my query
Still a good post. Solved my problem too.
Great post. It saved me from redoing a lot of work. Thank you!!!
Post a Comment