Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Service Broker Not Logging Messages


Service Broker Not Logging Messages

Author
Message
WoundedParrot
WoundedParrot
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 326
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.
johnweigel
johnweigel
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search