Cannot enable the Service Broker in database “msdb” due to GUID mismatch

Issue
=======
After downgrade from SQL server 2008 enterprise edition to standard edition, the DB mail failed to send the emails. The error in the logs were: On SQL server while enabling the service broker there is an error: Cannot enable the Service Broker in database “msdb” because the Service Broker GUID in the database (33D94A8D-0B38-411F-9E00-85CE0AB3FD8C) does not match the one in sys.databases (3B73AF92-9906-44AA-A7A3-A5B6495F3032).

Steps to resolve the issue
===========================

1) Alter database msdb set  new_broker;  which was taking long time to run
2) Ran select * from sys.dm_exec_requests and cam to know that the issue was related to blocking which was being done by some sqlagent job
3) stopped sql agent and the command ran successfully.
4) enabled the service broker by running the command  Alter database msdb set  enable_broker;
5) After that tested the dbmail but there was still the some error:

Exception Information<nl/>===================<nl/>Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException<nl/> Message: Mail configuration information could not be read from the database.<nl/> Data: System.Collections.ListDictionaryInternal<nl/> TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)<nl/> HelpLink: NULL<nl/>Source: DatabaseMailEngine<nl/><nl/>StackTrace Information<nl/>===================<nl/> at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)<nl/> at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)<nl/> at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)<nl/> at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)<nl/><nl/>2) Exception Information<nl/>= ==================<nl/>Exception Type: System.IndexOutOfRangeException<nl/>Message: timeout<nl/>Data: System.Collections.ListDictionaryInternal<nl/> TargetSite: Int32 GetOrdinal(System.String)<nl/>HelpLink: NULL<nl/>Source: System.Data<nl/><nl/>StackTrace Information<nl/>===================<nl/> at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)<nl/>   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)<nl/> at System.Data.SqlClient.SqlDataReader.get_Item(String name)<nl/> at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID),

6) Seemed like there was change in metadata after the upgrade so, recommended the customer to manually run the script run the script C:Program FilesMicrosoft SQL ServerMSSQL10.TEST2K8MSSQLInstall sysdbupg.sql

7) after running the script the issue was resolved.

Advertisements

3 thoughts on “Cannot enable the Service Broker in database “msdb” due to GUID mismatch

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s