unreceivable messages
Hey guys
I have another weird looking problem
I have a queue with a certain number of messages waiting in it to be processed. When I try and receive a message from this queue, I get back an empty row; no message. However, when I try and select from this queue, I see all the messages in there. Even sp_spaceused reports that there are rows in this queue. The status of each unreceivable message is 2. What would be the reason why I cannot receive these message?
I should add that newer messages added to this queue are receivable, meaning that if I add another message to the queue, when I do a receive, I get this new message and not the older ones in the list.
Thanks
RETENTION on the queue is enabled. Status 2 is for the messages you SENT from services on that queue, I believe. Retained messages are for compensation logic, they can be retrieved using SELECT and are deleted when the owning dialog gets deleted.
HTH,
~ Remus
Uhmmm I dont think thats right. I copied the table below from BOL
The following table lists the columns in a queue.
| Column name | Data type | Description |
|---|
status | tinyint | Status of the message. For messages returned by the RECEIVE command, the status is always 0. Messages in the queue may contain one of the following values: 0=Ready1=Received message2=Not yet complete3=Retained sent message |
My messagesa are in state 2. And have been this way for a while. Retention is off on the queue. We have thousands of messags being sent to and from this queue everyday; we cant afford to have retention on.
From the table above, my messages are not yet complete.... what does this mean. And Seeing as they have been in this state for at least a day (there is no date column in the a queue so I cant be more precise), is there anything else I can do with these messages apart from ending their conversations?
Sorry, me bad. I haven't check the actual state table before posting.
'Not yet complete' means that this is a large message that was fragmented in traffic and not all fragments were received. In order to figure out why the rest of the fragments don't come, you'll probably have to find the message sender and look at the sender's transmission_queue transmission status for these messages.
If you have more than one possible sources for the sender, you'll have to look up sys.conversation_endpoints and find the far_service. In case you have more possible sender services with the same name, far_broker_instance will corespond to the service_broker_guid in the sender's sys.databases.
You should then find the sender's conversation endpoint (use the conversation_id value, which is the same for both the sender and the target). The sender's conversation_endpoint should have the message in the sys.transmission_queue and the transmission_status should show why is not sending the rest of the message fragments.
It is alos possible that you wont' find the sender endpoint at all. This would indicate that the sender was somehow killed before it finished sending all fragments, i.e. due to any of the following:
- END CONVERSATION ... WITH CLEANUP
- ALTER DATABASE ... SET NEW_BROKER
- database restore to previous point in time
HTH,
~ Remus
