Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Oct 24 '11 at 15:29

This question came from our site for professional and enthusiast programmers.

    
Even adding a step to explicitly ALTER DATABASE ENABLE BROKER while in single user mode doesn't help. –  emailgregn Oct 21 '11 at 16:02
    
I have checked this with both a file restore full db backup and restoring from a snapshot. –  emailgregn Oct 25 '11 at 9:49

4 Answers 4

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
share|improve this answer
    
Yup, that's what I'm seeing and that workaround works thank you. Unfortunately the sys.dm_broker_queue_monitors query doesn't work on my installation, the queues are all showing up INACTIVE. @@version = Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) –  emailgregn Nov 3 '11 at 12:06

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
share|improve this answer
    
But this is after issuing SET MULTIUSER WITH NO_WAIT –  emailgregn Oct 21 '11 at 20:31
    
No offense, but I trust the error message more than I trust your observation. –  Remus Rusanu Oct 25 '11 at 0:40
    
No offense taken. I have a reproducible test script though. `` –  emailgregn Oct 25 '11 at 9:24
1  
[link]pastebin.com/zJ1QxMWQ –  emailgregn Oct 25 '11 at 9:30
    
Thank you for persevering with this, your script works as described. However adding a snapshot and restore to your repo script causes the original problem of messages remaining in the transmission queue.link –  emailgregn Nov 1 '11 at 14:25

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
share|improve this answer
    
Thank you, just tried those but still no joy. This seems to be a workaround but I have no idea why. ` USE master GO -- Drop to single user mode ALTER DATABASE [Accounting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE Accounting from DATABASE_SNAPSHOT = 'Accounting_Snapshot' WITH ENABLE_BROKER; GO ALTER DATABASE Accounting SET MULTI_USER, ENABLE_BROKER WITH NO_WAIT GO -- single user mode again ALTER DATABASE [Accounting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- multiuser with ENABLE_BROKER again ALTER DATABASE Accounting SET MULTI_USER, ENABLE_BROKER WITH NO_WAIT GO ` –  emailgregn Oct 25 '11 at 12:34

Try this,

ALTER DATABASE database_name 
SET MULTI_USER, ENABLE_BROKER; 

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

share|improve this answer
    
Thank you. I hadn't tried that. Unfortunately, still no success. Interestingly, I'm seeing sys.transmission_queue.transmission_status = "...single user ..." while getting sys.databases.is_broker_enabled=1 and user_access=0 and user_access_descr=MULTI_USER –  emailgregn Oct 25 '11 at 9:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.