Sql-server – The broker in the sender’s database is in single user mode

service-brokersql-server-2008

Could you please help with why am I getting this sys.transmission_queue.transmission_status message after restoring from a snapshot?

The broker in the sender's database is in single user mode. Messages cannot be delivered while in single user mode.

This is what I've tried without success:

  • Drop down to single user mode
  • Restore from a snapshot WITH NEW_BROKER option
  • Return to multiuser mode WITH NO_WAIT
  • Check sys.databases.is_broker_enabled and sys.service_queues.is_receive_enabled
  • Send a message which will get stuck in the transmission queue complaining about single user mode.

Is there another DMV or catalog to query to find the status of the service broker?

Thank you.

Best Answer

After a database restore, Service Broker queues with activation do not appear to have their queue monitors set back up properly, even if WITH ENABLE_BROKER is specified during the restore.

Explicitly re-enabling service broker causes the queue monitor to get recreated properly. If anybody has a better workaround, I'd love to hear it.

You can check for the problem by querying dm_broker_queue_monitors for any queues that are in the undocumented state 'DROPPED' that do not also have corresponding queues in a different state (e.g. 'INACTIVE').

If this query comes up with anything, you have a broken queue:

select database_id,queue_id,[state] from sys.dm_broker_queue_monitors m
 WHERE state='DROPPED' 
   and not exists (select 1 from sys.dm_broker_queue_monitors m2 
                    where state <> 'DROPPED' 
                          and m.queue_id=m2.queue_id 
                          and m.database_id=m2.database_id)

You can fix it by re-enabling service broker (yes, even though it is enabled already; re-enabling it will cause the queue to get rebuilt properly.) You need to be in single user to enable service broker:

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [dbname] SET ENABLE_BROKER WITH NO_WAIT
ALTER DATABASE [dbname] SET MULTI_USER
Related Question