Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Service Broker Not Logging Messages Expand / Collapse
Author
Message
Posted Thursday, May 5, 2011 8:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:30 AM
Points: 182, Visits: 270
Hi All

I've had Service Broker set up between 2 SQL 2005 instances for some months now, and it was working perfectly. However since then, and for no reason I can think of it has stopped working.

This is the case through the Dev, Test and Live environments.

Unfortunately, it hasn't given me any information to troubleshoot. There are no rows in the sys.transmission_queue table at the originating instance, nor are there any records in the receiving queue.

I'm unaware of anything that may have changed when it failed, apart from one element within the transmitted XML including 2s as well as 1s, but this was a recent change and only in dev.

I have tried to recreate everything from scratch, but this hasn't helped. As far as I can tell, the messages are being sent and received correctly (SQL Profiler on the destination instance reports inbound messages and Remote Message Acknowledgement, but I cannot see the SP being called that logs the messages into my table.

I've been searching the internet for a couple of hours for more troubleshooting tips but I haven't had much luck, is anyone aware of any further steps I could take?

Thanks in advance.
Post #1103927
Posted Friday, May 25, 2012 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 10:33 AM
Points: 1, Visits: 10
Is your queue active? = it might have been disabled due to errors. Run the following proc in your DB to see if the is_receive_enabled or is_enqueue_enabled are both set to 1 for the queue with issues. If they are 0, then run the following code to reactivate the queue.

ALTER QUEUE YourQueueName WITH STATUS = ON
Post #1306783
Posted Friday, May 25, 2012 2:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Try setting up the receiver to RETAIN the messages so that you can see for sure if they are actually getting that far. If they do not show up after that, then something is blocking/intercepting the messages after they have been received by the target Server, but before they are being put into the target queue.

If they DO show up after that, then you know that something is happening to them after they are queued to the target queue. Mostly likely causes there are either that Poison Pill protection is auto-disabling the queue, OR that your receiving/activation proc is actually RECEIVE-ing the messages, but discarding them and then re-excuting the RECEIVE (which would prevent you from seeing any new procedure activations).


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1306810
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse