sys.transmission_queue not responding

Question

SELECT

top(1) *

FROM

sys.transmission_queue

When running this query it doesnt repond and i have dad to cancel the query after 2 hours. Tried several other attempts and the problem is the same. the sysxmitqueue has 643731428 messages in which is a lot less than where it was last week with 2.2 billion messages where the query was responding. The sql server is 2005 and isnt under significant pressure. any ideas whats causing this query to not respond

Answer 1

Hi waynetheron,

If others are using this view and the transaction is not committed, your query will be blocked.

You can check which statements are blocked by running this:

select cmd,* from sys.sysprocesses 
where blocked > 0 

It will also tell you what each block is waiting on. So you can trace that all the way up to see which statement caused the first block that caused the other blocks. 

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

hi

already checked that and there is nothing blocking, it is a very strange problem.

Answer 3

This appears to be the issue with your service broker. Can you disable the broker and then try querying this view. If the problem presist then check your error log to see if anything related to service broker app logged inside SQL Server error log.

Answer 4

waynetheron,

This issue is really odd. If there is no block on the view, I can only suppose this is a performance problem. Please ensure your server is not in high workload. I would like to suggest you to use SQL Server profiler to track this issue, follow this kb article:
http://support.microsoft.com/kb/298475/en-us

If there is no performance issue, suggest you to submit a feedback to MS. For submitting a feedback to Microsoft such issues, I would recommend to submit it to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft Connect.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Hi,

So the real question here is why do you have 643 million or 2.2 billion records in your sys.transmission_queue? This means that the message is not getting to the target or if the message is arriving at the target the Ack is not making it back to the initiator. When a message is sent from the initiator a copy of the message is placed in the sys.transmission_queue. SSB then goes through an internal classify process to determine where to send the message. It looks up the to_service name from the sys.routes table and finds the tcp://address:port to route the message. Winsock api connect to the remote server and establish a stream and deliver the message. When the message arrives at the target it also goes through an internal classify process to find the database, service and queue to place the message into. Then an Ack is created and the classify process again looks for a route in sys.routes to deliver the message back to the inititator. Once the Ack is received at the initiator the message is removed from the sys.transmission_queue. If the Ack does not make it back to the initiator, the message will be automatically resent at varying intervals. Because you have so many messages in your sys.transmission_queue this indicates a problem either in your design, network or configuration. I would also check the amount of conversations you have in the sys.conversation_endpoints queue.

To query the sys.transmission_queue and sys.conversation_endpoints you would either add a nolock hint to the queries or hit the internal tables directly. Below are some queries to try:

--Messages pending in transmission_queue:
select p.rows from sys.objects as o join sys.partitions as p on p.object_id = o.object_id where o.name = 'sysxmitqueue'

--Total number of conversations in the database:
select p.rows from sys.objects as o join sys.partitions as p on p.object_id = o.object_id where o.name = 'sysdesend'

Hope this helps some,

Bill -- Microsoft CTS