March 29, 2013 at 7:36 am
I have five queues that are automatically disabling themselves after being re-enabled. I've been running broker for about two years without such a problem, not sure where to begin troubleshooting.
The only errors in sys.transmission_queue say "One or more messages could not be delivered to the local service targeted by this dialog."
Can anyone help point me in the right direction?
March 29, 2013 at 7:49 am
Sounds like a poison pill. After so many tries of processing the message, SB disables the queue. Are you able to manually send messages to the queues that SB isa having a problem with.
March 29, 2013 at 7:53 am
Hm. Is there a way I could enable the queue to manually send a message without the activation procedure causing it to disable again instantly?
March 29, 2013 at 8:12 am
I set the status of a queue to ON and turned activation OFF and ran its activation SP manually. So far it seems to be processing perfectly fine. I'm not sure what the deal is.
March 29, 2013 at 8:14 am
The second I set activation back on my Activation SP failed with a "Queue is currently disabled" error
EDIT: All of the transmission_queue messages for the queue I enabled were sent to that queue successfully when it wasn't disabled.
March 29, 2013 at 8:21 am
I managed to process all of the messages in one of the queues manually without issue...but if I'd set Activation = ON it would have failed. It's very curious.
March 29, 2013 at 2:11 pm
I think I'm closer to the source of the issue. When I try to run the activation procedure as the service broker user, I get this output:
(1 row(s) affected)
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
Msg 3930, Level 16, State 1, Procedure NewCat_InternalActivation_tblBidItemsInsertUpdate, Line 48
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3930, Level 16, State 1, Procedure NewCat_InternalActivation_tblBidItemsInsertUpdate, Line 56
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3930, Level 16, State 1, Procedure NewCat_InternalActivation_tblBidItemsInsertUpdate, Line 21
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3930, Level 16, State 1, Procedure NewCat_InternalActivation_tblBidItemsInsertUpdate, Line 24
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
April 1, 2013 at 2:15 pm
For anyone who might read this later:
When I did EXECUTE AS and ran as the user the queue runs as ('dbo') I found out I could not use Database Mail. This happened because I moved the database to a different drive. Once taken offline, the TRUSTWORTHY setting for the DB was set back to the default of OFF, which did not allow for the DB user to make use of its security privileges in the msdb database (the database that controls DBMail security). The fix for me was to run:
ALTER DATABASE DbName SET TRUSTWORTHY ON
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply