SQL Server Forum / Other Technologies / Service Broker / May 2006
Activation
|
|
Thread rating:  |
Richard - 17 May 2006 20:40 GMT I need to temporarily stop an activated queue (alter with status = off). The queue continues to accept rows, but when I start the queue again (alter with status = on), the queue entries don't get processed. How do I resume normal operations so that the queued entries get processed?
Thanks, Richard
Remus Rusanu [MSFT] - 17 May 2006 21:13 GMT ALTER QUEUE ... WITH ACTIVATION (STATUS = ON) will start activation. Make sure you don't use ALTER QUEUE ... WITH STATUS = ON which is different (it enables the queue, not the activation ont he queue).
 Signature This posting is provided "AS IS" with no warranties, and confers no rights.
HTH, ~ Remus Rusanu
SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>I need to temporarily stop an activated queue (alter with status = off). >The queue continues to accept rows, but when I start the queue again (alter [quoted text clipped - 3 lines] > Thanks, > Richard Richard - 17 May 2006 21:58 GMT That worked. Thanks.
The reason I ran into that is because when a queue errors 5 times its status (is_receive_enabled and is_enqueue_enabled) gets set off, and other valid entries stay in the queue. When I set the status on (alter queue... with status on) the remaining valid queue entries don't get processed. How does the queue get put back into a state so that valid entries are processed via activation?
> ALTER QUEUE ... WITH ACTIVATION (STATUS = ON) > will start activation. Make sure you don't use [quoted text clipped - 9 lines] >> Thanks, >> Richard Remus Rusanu [MSFT] - 17 May 2006 23:06 GMT ALTER QUEUE ... WITH STATUS = ON/OFF controls the is_receive_enabled and is_enqueue_enabled columns. ALTER QUEUE ... WITH ACTIVATION (STATUS = ON/OFF) controls the is_activation_enabled column.
The activation starts processing messages when all three columns are 1. BTW, is_receive_enabled and is_enqueue_enabled values are always identical in SQL 2005. When the queue is disabled because of poison message detection, the is_receive_enabled/is_enqueue_enabled columns are changed to 0 (the queue is disabled). The is_activation_enabled should be left at 1. When the is_receive_enabled/is_enqueue_enabled is changed from 0 to 1 (he queue is enabled back), the activation should start.
 Signature This posting is provided "AS IS" with no warranties, and confers no rights.
HTH, ~ Remus Rusanu
SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> That worked. Thanks. > [quoted text clipped - 18 lines] >>> Thanks, >>> Richard Richard - 18 May 2006 02:05 GMT That kind of describes the issue. Here's a little more detail on what happens.
* send 2 good transactions, everything is good * send 5 bad transactions, queue gets disabled * send 3 more good transactions, they stay in the transmission queue and I captured the error "The service queue "MyQueue" is currently disabled." * execute the command "alter queue... with status on" * send 2 more good transactions, they process normally * the 3 good transactions that were enqueued when the queue status was off are still in the transmission queue
How can I programmatically process the 3 rows that are seemingly stuck in the transmission queue?
> ALTER QUEUE ... WITH STATUS = ON/OFF controls the is_receive_enabled and > is_enqueue_enabled columns. [quoted text clipped - 32 lines] >>>> Thanks, >>>> Richard Remus Rusanu [MSFT] - 18 May 2006 04:09 GMT The messages in the transmission queue will be retried after some time (less than a minute). How long did you wait for the messages to be enqueue and processed?
 Signature This posting is provided "AS IS" with no warranties, and confers no rights.
HTH, ~ Remus Rusanu
SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> That kind of describes the issue. Here's a little more detail on what > happens. [quoted text clipped - 48 lines] >>>>> Thanks, >>>>> Richard Richard - 18 May 2006 22:18 GMT The rows in the transmission queue are processed again, but they are never removed from the transmission queue and are processed over and over. "Processing" includes writing the message_body to another table. The last code to execute is "end conversation with cleanup." I don't get any client or logged errors, and this is all within a transaction that's within a try/catch -- the catch logs errors to another table.
Any ideas?
> The messages in the transmission queue will be retried after some time > (less than a minute). How long did you wait for the messages to be enqueue [quoted text clipped - 52 lines] >>>>>> Thanks, >>>>>> Richard Remus Rusanu [MSFT] - 18 May 2006 22:32 GMT NEVER use END CONVERSATION WITH CLEANUP in a program. See http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx
 Signature This posting is provided "AS IS" with no warranties, and confers no rights.
HTH, ~ Remus Rusanu
SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> The rows in the transmission queue are processed again, but they are never > removed from the transmission queue and are processed over and over. [quoted text clipped - 62 lines] >>>>>>> Thanks, >>>>>>> Richard Richard - 19 May 2006 03:21 GMT Thanks for your all of your help.
When a row is first queued, the row is successfully processed, but then it seems to attempt to be processed 5 more times until the queue is disabled. In the following snippet, the raiserror causes an exception five times after the first successful process. This does not occur when "with cleanup" is part of the "end conversation" command. I know I'm doing something wrong, but can't figure out what.
begin try; begin transaction;
waitfor ( receive top( 1 ) @ConversationHandle = conversation_handle, @QueueMessage = message_body from q_Request ), timeout 12000
if @@rowcount !> 0 begin rollback return @ERROR_NOTHINGQUEUED end
if @QueueMessage is null raiserror ( 'null queue message error', 18, 5 )
end conversation @ConversationHandle --with cleanup
commit end try
begin catch rollback exec dbo.p_ExceptionLog return @ERROR_UNHANDLEDEXCEPTION end catch
> NEVER use END CONVERSATION WITH CLEANUP in a program. > See http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx [quoted text clipped - 65 lines] >>>>>>>> Thanks, >>>>>>>> Richard Roger Wolter[MSFT] - 19 May 2006 05:12 GMT The only reason a message would stay on the queue to be reprocessed after being received would be if the transaction that contains the RECEIVE is rolled back
 Signature This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
> Thanks for your all of your help. > [quoted text clipped - 107 lines] >>>>>>>>> Thanks, >>>>>>>>> Richard Remus Rusanu [MSFT] - 19 May 2006 19:18 GMT You must check the message type received. Any application should be prepared to deal with the system message types (EndDialog, Error and Timer). Add a @message_type_name variable (of type sysname) and receive into it the message_type_name column. Check this message type in your processing. You are raising error when message body is null. What probably happens is that you're receiving the EndDialog message (which does have a NULL body) and you are raising because of it. When you're using END WITH CLEANUP, the EndDialog message is not sent (hence the apparent 'good' behavior).
The complete names of the system message types are: [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] [http://schemas.microsoft.com/SQL/ServiceBroker/Error] [http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer]
 Signature This posting is provided "AS IS" with no warranties, and confers no rights.
HTH, ~ Remus Rusanu
SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> Thanks for your all of your help. > [quoted text clipped - 107 lines] >>>>>>>>> Thanks, >>>>>>>>> Richard Richard - 19 May 2006 21:15 GMT That was the problem. Thanks!
> You must check the message type received. Any application should be > prepared to deal with the system message types (EndDialog, Error and [quoted text clipped - 123 lines] >>>>>>>>>> Thanks, >>>>>>>>>> Richard
|
|
|