Service broker error

• WhiteLotus

  SSChampion

  Points: 12122

  More actions

  January 5, 2016 at 4:20 pm

  #314640

  hi Guys ...

  I got Service Broker error from a few days ago.. To troubleshoot I run this command from CommandPrompt

  Ssbdiagnose –E –d DB1 CONFIGURATION FROM SERVICE Init_Service to SERVICE target_Service

  The result are 2 errors :

  Error 1 : DB1 contract DEFAULT is not bound to service target_service

  Error 2 : Db1 The master key was not found

  Should I create new Master key by following this solution that I got from Google :

  First Check your service broker queue by right clicking the queue and View Transmission Queue or simply use this query

  SELECT *, casted_message_body = CASE message_type_name WHEN 'X'

  THEN CAST(message_body AS NVARCHAR(MAX))

  ELSE message_body

  END

  FROM [DATABASE_NAME].[sys].[transmission_queue]

  If you find any data in here then transmission_status column will have the reason for this.

  If the broker is not playing its role, I would create NEW_BROKER with following query

  USE [master]

  ALTER DATABASE [DATABASE_NAME] SET NEW_BROKER

  Then Enable the BROKER with TRUSTWORTHY set to ON

  ALTER DATABASE DATABASE_NAME SET ENABLE_BROKER;

  ALTER DATABASE DATABASE_NAME SET TRUSTWORTHY ON;

  Finally, dropping the master key and Creating New master key and encrypt by new password:

  ALTER AUTHORIZATION ON DATABASE::DATABASE_NAME TO [SA];

  DROP MASTER KEY

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '79HGKJ67ghjgk^&*^fgj'

  GO

  Any feedback are much appreciated

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  January 6, 2016 at 2:58 am

  #1849779

  I do not know the ssbdiagnose utility. I do however have some experience with service broker and with troubleshooting common errors.

  First - you do indeed need a master key in the database to use service broker, so just go ahead and create one.

  Second - if you still have problems after that, then script out and post all the service broker objects you use (it's okay to anonimyze server names if they exist in those scripts, if you are worried about security), plus the code you use to send messages on the queue and to read them. Also describe the generic setup - are you using service broker in a single database, between databases on the same instance, or between databases on different instances/servers?

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• WhiteLotus

  SSChampion

  Points: 12122

  More actions

  January 12, 2016 at 5:43 pm

  #1851458

  Thanks so much for the reply . I really appreciate it and Sorry for the late reply .

  I will try it in my production this week and see how it goes .

  Will let you know !

  Cheers