Thursday, 22 September 2011

Service Broker GUID does not match the one in sys.databases

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.

8 comments:

Jay Allard said...

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.

Anonymous said...

Good Post.it solved my problem.

Anonymous said...

Thanks for the post buddy. It helped.

Anonymous said...

it worked for me too

RadhaKrishnan Velusamy said...

Thanks Buddy.. You saved me from firing..

Alex Bransky said...

Thanks!!

Satya Ranjan said...

thanks buddy ,it helped me.thanks again

Anonymous said...

Thank you,it solved my query

Post a Comment