Database Mail and Service Broker Issue

Recently I faced an issue with my Database mail in one of the test server. When I was trying to send test mail using DB mail I was getting below error:



TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:

Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery. (Microsoft SQL Server, Error: 14650)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=14650&LinkId=20476

I tried to enable Service broker using below command:

Note: You need to stop SQL server agent before running below command

ALTER DATABASE msdb SET ENABLE_BROKER

But I was getting below error:

Msg 9776, Level 16, State 1, Line 1

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (95101104-EDB7-4C9C-8218-930A18B127EC) does not match the one in sys.databases (7E03C2E9-2C9A-4384-BDDA-8BF6971D3D4C).

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

 

When I checked sys.databases and found that the Service Broker GUID of MSDB is different from the one in sys.databases.

Reason: Recently I was testing a DR test where I restored backup of MSDB Database from other server to my Test Server and that was the reason my MSDB database Service broker GUID was not matching with the one in sys.databases

Then I ran below command to create New Broker for MSDB Database.

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Once I ran above command, new Service Broker GUID created and resolved my DB mail issue.