Adam Hardy-416657 (8/28/2014)
[snip] only to find it traced back to the original Receive only getting 30-60 messages at a time.
So you're definately not losing them due to communication loss or poison message storage. Good, that means you've nailed it down to the pickup. Excellent troubleshooting.
The one thing that would suggest that this wasn't because of un-communicated/responding messages is that when I didn't get a timely response to my AskSSC question, I had to turn the activation back on.
Most folks won't go near troubleshooting Service Broker simply because of the few million moving parts making it tough to do without direct access to the machine. Add to that the majority of people playing with it are typically new to messaging in general and it turns into a mess. AskSSC lends itself to quick, one-shot answers. I'm unfortunately not surprised you didn't get a response there.
Hopefully even if I can't help you others on this board with more expertise will pop into the conversation with what we clarify and get you to a solution, or at least an understanding.
I watched the message count in the queue and it counted down to zero after a minute or two which to me indicates that it picked up ALL the messages in the queue in increments.
That's odd. At first glance, and a skim of the code you posted later (that's going to take me a bit to grok, obviously, in my spare time), I can't see any direct reason you should be getting chunked pickup. A seemingly random question, what's the server and/or database's default isolation level? I'd assume it's read committed but want to confirm, and I didn't see a directly set one in your activation proc above.
I don't think there is anything particularly non-default about the queue. When I set this up, I read a variety of blogs etc about how to setup "one-way" messaging properly (meaning not fire and forget). I also implemented conversation session re-use as this was recommended for high traffic.
Nothing particularly strange then. Good.
You're probably going to need the script I think to help further. I'll try to sanitise it and post it up.
After skimming down it, I'm going to need to sink my teeth into it before I can intelligently discuss your setup further. Unfortunately, I'm not going to get the chance to do that this evening. I know you're feeling pressure on this, so I'll try to sit down with it in the morning if business doesn't end up with another one of its emergencies and really dig into it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA