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

Been Learning Windows Workflow

Sorry for the lack of posts lately!  I’ve been working on a project involving Windows Workflow and it’s been consuming all my time.  I’ve also had a run of personal events that have been taking up any free time that I’ve had.  The nice weather of the spring season has also been calling me to get outdoors.  Anyways, I’m back and ready to blog all my experiences with Workflow.  So, be ready for many new posts in the next few days.

Posted in | 0 comments