Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
7
Impact
~983 people reached

  • 0 posts edited
  • 0 helpful flags
  • 14 votes cast
Dec
14
awarded  Nice Question
Nov
17
comment Service broker was backed up, now receiving, but doesn't seem to be processing
@RemusRusanu I'm not sure - I met all 4 of the criteria at the time (I was checking sys.service_queues, though not dm_broker_queue_monitors), and the activated SP had been running fine for months before. I do remember checking sp_whoisactive, which normally shows a couple spids as active, and it didn't have anything. Guess I need to reproduce the problem and check queue_monitors. I do know that at one point I tried turning off and on the queue, so maybe it was stuck in that state. Will try and duplicate.
Nov
17
comment Service broker was backed up, now receiving, but doesn't seem to be processing
@BenThul when we were having the problem i manually ran 4 SPIDs like you suggested. In 2 or 3 minutes they wrote a total of 32 rows to my final table. Granted, that's more than the activated SP "attached" to the queue were doing, so that hints at something else amiss. Apparently I'm going to have to try and duplicate it.
Nov
16
awarded  Commentator
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
@BenThul added the Activated Stored Procedure code to the question. FWIW, I don't use 1=1. I spoke with Remus Rusanu about it a couple of years back, and he spoke on how there's no reason whatsoever to use it, and I modified mine accordingly. That being said, I probably ought to look at changing it to perform batches of 100/1000, for speed. But until it got backed up, it had been working like a champ, so I was loathe to touch it.
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
@shawnmelton thanks for tweaking the formatting.
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
@BenThul okay, when the queue is on, after a minute I do see the 5 readers I should have. However, when I tried running a test for a couple of minutes, where I enabled the queue and manually ran 4 loops of the activated SP, I only wound up with 32 rows inserted into the final table. Granted, not all rows make it in, but it ran enabled for several hours without any rows making it in - which seems odd, right? If I run it by hand, why does it process so many more than the activated SP? My only idea is that it's so swamped catching, it doesn't have time to process (or is blocked on RECEIVE).
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
@RobertLDavis it's not poisoned message - checked my table. I have a monitor checking for "queue disabled", too, and it's fine. It'll turn on and accept messages just fine, though. just won't process them. Sys.transmission_queue is empty.
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
Comment first for @BenThul : Nothing in sys.dm_broker_activated tasks. It's not trying to process all at once - it's doing one at a time. ("receive top (1) [...]from EnAudit_SBQueue"). And I can't run while queue is disabled. And if queue is enabled, it's growing fast enough (10mb every couple seconds) that it's going to blow out the drive again, even with 4 spids running the processing in a loop.
Nov
16
asked Service broker was backed up, now receiving, but doesn't seem to be processing
Oct
29
awarded  Scholar
Oct
29
accepted Better parallelization than a sequence container in SSIS 2008?
Oct
28
awarded  Editor
Oct
28
revised Better parallelization than a sequence container in SSIS 2008?
changed title to make it more obvious what I'm trying to do.
Oct
28
comment Better parallelization than a sequence container in SSIS 2008?
I know. And I use SB for several other things. But I'm trying to avoid it for this.
Oct
28
asked Better parallelization than a sequence container in SSIS 2008?
Mar
15
comment Get object script in SQL Server via T-SQL?
ObOtherWayToDoIT. I've been using Event Notifications for the past couple years with great success. (So great I now present on it at SQLSatrudays). Here's my current presentation, slide deck, demos, code repository and all. sqlsaturday.com/viewsession.aspx?sat=362&sessionid=25219
Mar
15
comment Get object script in SQL Server via T-SQL?
What he said. Here's a good article on doing it via Powershell: simple-talk.com/sql/database-administration/…
Sep
24
awarded  Autobiographer
Oct
5
awarded  Supporter