none
Conversations stuck in state of CONVERSING and not processing messages

    Question

  • Hey, all.  I'm having an issue where we have a number of table triggers which use service broker to document any DML statements run against those table.  The changes are inserted via service broker in to some tables we use for this auditing. When I trigger these events I can see the conversations with service broker started by querying sys.conversation_endpoints.  However, they stay in a state of CONVERSING and never seem to be processed by service broker until almost a day later.  I've tried disabling/re-enabling service broker on the database (yes, this is in Test), and also purging the conversations and trying again, but it seems there is a serious delay in getting service broker to process them.

    Is there a way where I can force service broker to process these messages?  And what is an effective way to troubleshoot performance issues affecting service broker?

    Any help is greatly appreciated!

    Friday, December 20, 2013 6:44 PM

All replies

  • Just sending messages isn't enough to cause them to be processed.  First you need to ensure that the messages are being delivered to the target queue (which may require routing to another database or broker instance), then ensure that they are processed.  The processing might use internal activation, external activation, or a polling or manual process.

    See in general:

    Troubleshooting (Service Broker)

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, December 20, 2013 6:56 PM
  • You're right, David.  The problem seems the messages aren't making it to their designated queue.  I've already confirmed that service broker is still enabled and that the route for the target queue exists.  I'm looking in to using ssbdiagnose next but any additional advice would be greatly appreciated.  Thanks!
    Friday, December 20, 2013 7:18 PM
  • After using ssbdiagnose.exe by executing the following command:

    "ssbdiagnose CONFIGURATION FROM SERVICE //SSB_Initiator TO SERVICE //ProdSvc/SSBService_AsynchTrg -S <servername> -E -d <dbname> > ssberrors.txt"

    SQL is returning the following error for most of the databases hosted on this instance (in the example provided my database is called "Cust1_Prod"):

    "Cust1_Prod     Service Broker GUID is identical to that of database Cust1_Prod on server SQLSRVR."

    Why is it reporting a duplicate GUID on the same database?? This error doesn't make much sense and I'm not certain how to resolve it.


    • Edited by MRWjrDBA Monday, December 23, 2013 3:22 PM Remove proprietary info.
    Monday, December 23, 2013 3:21 PM
  • Also, I have tried fixing this by running ALTER DATABASE NEW_BROKER and it doesn't make a difference.
    Monday, December 23, 2013 3:23 PM
  • Is the target service in a different database?  Different instance?  Is the message in the sys.transmission_queue?  Is there an error mentioned there?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, December 23, 2013 3:55 PM
  • Hey, David.  The service is on the same server, instance and database.  It's very self-contained.  And, no, unfortunately I don't see anything in the sys.transmission_queue table.

    Monday, December 23, 2013 7:57 PM
  • Good. That simplifies things. Start by manually issuing BEGIN DIALOG and SEND and verify that the message is delivered without error.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, December 23, 2013 8:23 PM