I'm using the Service Broker to parallize my processes (I know that the Service Broker was not designed for that purpose), however it's working quite well.
I use the broker procedure to start procedures which all process all a part of the workload. When the procedure fails because of a lock timeout (or for that concern, for whatever reason), I rollback the transaction (which also roll back my message received on the queue so that it can be retried at a later time.). And this is where my problem lies, if there are 5 sequential rollbacks of messages then the poison message detection kicks in and disables the queue, stopping all the processing. :(
Is there a way to disable poison message detection? I have implemented my own stop-mechanism through a counter system on a per sub-task system so if I could disable poison message detection that would be ideal.
If this is not possible is there a way to turn the queue back on automatically so that it will continue processing the messages on the queue?
Cheers,
Peter.
Not sure if this helps your situation but you could do a SAVE TRANSACTION after you have received it off the queue.
Then, if whatever condition happens for it to rollback you can rollback to the save point, that way all work will be rolled back but the poison message is committed off the queue, you could also write the message to a table afetrwards, that way your queue will keep working and you can save the poison message to check them out later, and possibly put a notification trigger on the poison message table so that you know when they happen so you can check them out, hope that helps ?
Thanx
|||In SQL Server 2005 is not possible to disable poison message support. The main problem with disabling poison message detection is that when something goes wrong and a real poison message comes into a production system, it takes the service down. We really encourage developers to avoid using rollbacks in activated procedures.
For instance, in your case, maybe is better to save the message being processed (e.g. into a table, or turn on message retention to use the queue itself w/o the need for a table) and then send yourself a timer message (BEGIN CONVERSATION TIMER with a small delay) and commit. When the timer fires a message is sent to your own service and you are going to be activated again. the activated procedure reacts to the timer message by looking up the saved message and trying to process it again. If it fails again, set a new timer ang commit. This way you can set up a more reasonable retry policy than rollback and retry immedeatly.
As about a way to turn the queue back on automatically, yes, there is a way. When a queue is deactivated, it can generate an event notification:
CREATE EVENT NOTIFICATION [QueueDisabled]
ON QUEUE [<queue name>]
FOR BROKER_QUEUE_DISABLED
TO SERVICE 'QueueDisabledServiceHandler', 'current database';
HTH,
~ Remus
I have the following code for reactivation:
CREATE QUEUE q_task_detail_receive_disabled_handler
CREATE SERVICE s_task_detail_receive_disbaled_handler ON QUEUE q_task_detail_receive_disabled_handler
CREATE EVENT NOTIFICATION q_task_detail_receive_disabled
ON QUEUE q_task_detail_receive
FOR BROKER_QUEUE_DISABLED
TO SERVICE 's_task_detail_receive_disbaled_handler', 'current database';
create procedure p_enable_queues
as
begin
ALTER queue q_task_detail_receive WITH STATUS = ON
end
go
ALTER QUEUE q_task_detail_receive_disabled_handler
WITH ACTIVATION (
STATUS = ON, -- Activation turned on
PROCEDURE_NAME = p_enable_queues, -- The name of the proc to process messages for this queue
MAX_QUEUE_READERS = 1, -- The maximum number of copies of the proc to start
EXECUTE AS SELF -- Start the procedure as the user who created the queue.
);|||
Is the notification being delivered into q_task_disabled_handler?
You must RECEIVE from q_task_receive_disabled_handler in the p_enable_queues. This is a general rule for activated procedures, they must RECEIVE from the queue, even if they don't care about the message.
HTH,
~ Remus
