I have an application that is using the Service Broker is SQL 2008. About once a day the database's performance starts take a noticeable hit and I have determined that this is because of the Service Broker. If I hard reset all broker connections using the following commands:
ALTER DATABASE [RegencyEnterprise] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [RegencyEnterprise] SET ONLINE
Then the performance returns to normal until about the next day. I have also noticed that when performance is poor, running the following query returns a large number of conversations that are stuck in the STARTED_OUTBOUND state:
s.name as 'local service',
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;
Performance seems to be alright where there are plenty of items returned by this query. The only time when there are problems are when there are connections that are STARTED_OUTBOUND that stay stuck in this state.
Does anyone know what can cause the broker's connections to get in this state? Or what tools I could use to go about trying to figure out what is causing this? I currently only have a single web server that is registering to its notifications, so my scenario is not overly complex.
Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.
TechNet Community Support
The STARTED_OUTBOUND state represents the fact that SQL Server processed a BEGIN CONVERSATION for this conversation, but no messages have yet been sent . Do you the code in place to END DIALOG after you are done processing the messages ? I prefer to have an END CONVERSION in my recieve proc, as well as have a automatically activated CLEAN UP proc on the initiator queue with this code.
Have you tried using the SSBDIAGNOSE utility ? Use this utility's CONFIGURATION mode to verify your vervice broker set up. Then use it in RUNTIME mode by passing in the conversation handle of one of the messages's who state is SO and tell us what the report says - http://msdn.microsoft.com/en-us/library/bb934450.aspx
Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com