The broker in the sender's database is in single user mode

Question

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.

Answer 1

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

Answer 2

Try this,

ALTER DATABASE database_name 
SET MULTI_USER, ENABLE_BROKER; 

There is a catalog view sys.databases, use column user_access.

Answer 3

Messages cannot be delivered while in single user mode.

Isn't this clear enough?

Edit after repro

the repro you posted is incomplete, it uses objects not define din the repro (the database and the snapshot). I used this script to test something I believe is similar to your case:

USE master
GO

if db_id('Accounting') is not null
begin
    alter database Accounting set single_user with rollback immediate;
    drop database Accounting;
end
go

create database Accounting;
go

alter authorization on database::Accounting to sa;

use Accounting;
go

create queue q;
create service s on queue q ([DEFAULT]);
go

-- Drop to single user mode
ALTER DATABASE [Accounting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO


declare @h uniqueidentifier;
begin dialog conversation @h 
    from service [s]
    to service N's'
    with encryption = off;
send on conversation @h ('Hello, World');
go  

-- Give the queue a bit of time to process
WAITFOR DELAY '0:00:02';
GO
-- Take a look after sending a message
SELECT * FROM sys.transmission_queue; -- 1 row, transmission_status = "...single user ..."
GO

-- Retry same multiuser with ENABLE_BROKER
USE master
GO
ALTER DATABASE Accounting SET MULTI_USER, ENABLE_BROKER WITH NO_WAIT
GO

use Accounting;
go

-- wait for the message delivery
WAITFOR (receive cast(message_body as varchar(8000)), * from q), timeout 60;
GO

-- Look at xmit queue
USE Accounting;
SELECT * FROM sys.transmission_queue; -- 1 row, no error status.
GO

-- Look again at xmit queue, in 10 seconds
waitfor delay '00:00:10';
USE Accounting;
SELECT * FROM sys.transmission_queue; -- row is gone
GO

This script shows that:

• the message is not delivered (is delayed) when the database is in single user mode.
• the message gets delivered immediately after the database is placed in muti-user mode
• post delivery, the message is deleted asynchronously after a few seconds from the transmission queue

Answer 4

Don't set NEW_BROKER during restore. Try,

RESTORE DATABASE Accounting from DATABASE_SNAPSHOT = 'Accounting_Snapshot' WITH ENABLE_BROKER;
GO

And check the queue is enabled

ALTER QUEUE AsyncExecQueue WITH STATUS = ON, ACTIVATION (STATUS = ON); 
GO