Troubleshooting SQL Server 2005 Service Broker

My latest project has not only required me to use Workflow but also to create and use a Service Broker on SQL Server 2005.  Service Brokers are similar to MSMQs (Microsoft Simple Message Queues) except they are managed by SQL Server 2005.  For a great introduction to Service Brokers check out this intro on The Code Project.

Service Brokers are pretty simple to understand.  There are also many articles out there on how to set one up.  Most of this is pretty simple to setup as well.  However, there is not much out there on how to troubleshoot if something goes wrong.  In my case, I’m still not 100% sure what did go wrong..   However, I do know it is fixed now. 

The frustrating thing is that when I actual started the conversation and sent the message I got no errors.  All I knew was that the queue was still empty and my receiver never got the message. 

I stumbled across a great article on troubleshooting here.  This article helped me put together a series of queries that ultimately led me to determine the errors I was getting.  Here is the query I created:

-- This query get's incremented if your msgs are having errors

select p.*

from sys.objects as o

join sys.partitions as p on p.object_id = o.object_id

where o.name = 'sysxmitqueue'

 

-- Error messages in the queue are located in the transmission_status field

SELECT *

FROM sys.transmission_queue

 

SELECT conversation_handle, is_initiator, s.name as 'local service',

far_service, sc.name 'contract', state_desc

FROM sys.conversation_endpoints ce

LEFT JOIN sys.services s

ON ce.service_id = s.service_id

LEFT JOIN sys.service_contracts sc

ON ce.service_contract_id = sc.service_contract_id;

These queries give you all the system related info about the brokers.  Hopefully you can use this to troubleshoot your own Service Broker.  

Posted in |

3 comments:

  1. Anonymous Says:

    Great post.... Nice Explanation...

  2. Anonymous Says:

    Uh....absolutely very useful for me. Thank you very much for your great post. Good job.

  3. Anonymous Says:

    Can anyone recommend the top performing RMM utility for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: N-able N-central network tools
    ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!