sys.dm_broker_queue_monitors - what means status DROPPED?

Question

Hello! I'm trying to understand service broker errors that i have in log:

An error occurred in dialog transmission: Error: 9655, State: 3.

Error: 9736, Severity: 16, State: 0.

Can't find any information in the BOL and internet at ALL

Now, i'm trying to understand sys.dm_broker_queue_monitors that have many rows with DROPPED status, but there are no description of this status in the BOL. Can someone help me to find relative information?

Thanks!

P.S. Service Broker is the worst thing due a badest documentation!

---

Konstantin Knyazev

Answer 1

Hi Konstantin,

Right now, I have not found any document about it either. If there is any progress, I will give an update as soon as possible. Thanks for your understanding.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi,

sys.messages defines the 9655 and 9736 messages as:

--select * from sys.messages where message_id = <error_number>

--9655
The transmission queue table structure in database is inconsistent.  Possible database corruption.
--9736
An error occurred in dialog transmission: Error: %i, State: %i. %.*ls

For the 9655, state 3, this means that a missing message was found in the sys.transmission_queue. A state 1 means that a duplicate message was found. In either case the meta data is inconsistent. This would be a possible database corruption state. You should run "dbcc checkdb" on the database with a repair mode in order to bring the database back into a consistent state. You might loose data.

I suspect that the dialog hit the corruption in sys.transmission_queue and then raised the 9736 error.

Sys.dm_broker_queue_monitors has four states. Inactive, Notified, Receives Occuring, and Dropped.

Inactive - The queue is in an inactive state. In essence no receives are occuring on the queue or notifications to activate.

Notified - An state which allows additional max_queue_readers to activate if allowable. You need to have a receive occur to get to a notified state. All activation stored procedures should not exit without doing a receive. This is part of the process that trigges additional activation stored procedures to activate.

Receives occuring - A state inwhich a receive statement is executing on the queue. In essence messages are being dequeues\popped off the queue.

Dropped - We should not see a dropped state, but I suspect it has to do with the corruption in the sys.transmission_queue. I would address this first and then see if the state exists or if the queue still exists.

Hope this helps some.

Bill (Microsoft)

Answer 3

Hi! The problem is that i don't see any message in sys.transmission_queue, it is empty.

Thanks!

---

Konstantin Knyazev

Answer 4

Hi Konstantin,

Whether you see messages in your sys.transmission_queue depends on somethings. The real question is do you see messages arrive at your target queue?

Lets suppose you send a message from server1, database1 across the network to server2, database2. In this example on server1,database1 you will see messages in your sys.transmission_queue for a short duration (hopefully). Server1 will begin a dialog conversation or reuse a conversation, send the message and place a record in sys.transmission_queue on server1, database1. The message will travel across the network to server2. A classify process will occur to find the proper database with the correct service\queue. The message will be placed in the user queue on server2, database2. Next internally a ACK will be sent back from server2 to server1. Once the ACK is received on server1 then the message will be automatically removed from the sys.transmission_queue on server1, database1. Lets suppose that the ACK does not make it back to server1 (maybe improper route form server2 to server1). Then on server1 the message in sys.transmission_queue will be used to resend the message. The algorythm resends the message every 4, 8, 16, 32, 64 seconds until a ACK is receive. Once the ACK is recieved the message is removed from the sys.transmission_queue. In this situation since server2, database2 is not sending any user messages the sys.transmission_queue will be empty. If the process is happening very fast the sys.transmission_queue on server1 might appear empty.

Another situation where you will not see any messages in the sys.transmission_queue is if you are sending messages from an initiator service\queue to a target service\queue on the same instance of SQL or within the same database. The classify process will route the messages very quickly and you will not see any messages in your sys.transmission_queue.

So it will depend on your configuration and architecture if you see messages in your sys.transmission_queue. Hopefully you do not and you see your user messages arriving in the target queue successfully.

Hope this helps some,

Bill (Microsoft)

Answer 5

Thanks you for calrification, Bill!

I'm using service broker inside single database. And the big problem for me is that i don't see any message in any queue, but have regular error messages (see first message). And i even can't find queue that is a source of errors. The only thing that i can see is Database ID and futrhermore if i see Broker:Transmission event in Profiler i still can't detect source of problem.

best regards, Konstantin

---

Konstantin Knyazev

Answer 6

Hi Konstantin,

The first message in your posts says you have database corruption in the meta data for service broker. To progress you must either repair the corruption (see books online for "dbcc checkdb") and use one of the repair options to fix the corruption. If you dont care about the database, because you are just testing drop and recreate it. The reason you can find the queue that is the source of the error is because it probably does not exist, but the meta database says it does (ie corruption in the meta data).  Sorry I was unclean about this in prior posts.

In another reply I will post some same code for a single database for you to try in a clean database.

Bill

Answer 7

use

master

go

--create database

create

database [SSB_TEST]

go

--enable service broker for database

alter

database [SSB_TEST] set enable_broker with rollback immediate;

use

SSB_TEST

go

--create a database master key for sessions

create

master key encryption by password = '1Str0ngPassword'

go

--review sys.databases

select

name, database_id, is_master_key_encrypted_by_server, is_trustworthy_on, service_broker_guid, is_broker_enabled, is_honor_broker_priority_on from sys.databases

go

--create message type, ([DEFAULT] is a system message type)

create

message type EndOfStream validation = none;

create

message type RequestMessage validation = well_formed_xml;

create

message type ResponseMessage validation = well_formed_xml;

go

--create contract, ([DEFUALT] is a system contract)

create

contract EOSContract ([EndOfStream] sent by any, [DEFAULT] sent by any);

create

contract RRContract ([EndOfStream] sent by any, [DEFAULT] sent by any, [RequestMessage] sent by any, [ResponseMessage] sent by any);

go

--create queues

create

queue SendQueue

create

queue RecvQueue

go

--create services on queues, using [DEFAULT] system contract and user defined contracts

create

service SendService on queue SendQueue ([DEFAULT], [EOSContract], [RRContract])

create

service RecvService on queue RecvQueue ([DEFAULT], [EOSContract], [RRContract])

go

--create table to store information being delivered in the message

create

table MessageInfo (id bigint identity(1,1), info nvarchar(500), message_type nvarchar(500), queue_name nvarchar(500), dt_stamp datetime);

go

--basic manual sending of a message

--create a dialog conversation

declare

@ch uniqueidentifier

begin

dialog conversation @ch

from service [SendService]

to service 'RecvService'

on contract [DEFAULT];

--send a message using default system message type

send

on conversation @ch message type [DEFAULT] ('<Payload><Info>A test message</Info></Payload>');

--Check if message arrived at target queue

select

*, CAST(message_body as XML) from RecvQueue

Answer 8

Thanks for your attention!

It was first step, dbcc checkdb, and there are no errors at all.

I will investigate your sample some time later. Thank you very much!

---

Konstantin Knyazev