May 5, 2011 at 8:26 am
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.
May 25, 2012 at 12:48 pm
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
May 25, 2012 at 2:05 pm
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).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply