In my previous post I provided an example for setting up a Service Broker Queue. One of the issues you will most likely encounter soon after using Service Broker Queues is a message that fails when being processed. With Service Broker Queues it is strictly enforced that all requests in the queue be processed in order. The problem is that when an error occurs, it stops the queue and prevents any other messages from being processed until someone intervenes and fixes the the bad message (Usually a poor systems administrator who is called in the middle of the night!).
What if you are using the queue to process messages where order is not that important? If a message fails you would rather that the message be skipped and placed on a dead letter queue and not interrupt processing of the rest of the messages on the queue. Unfortunately service broker does not offer this feature, unlike many other queuing systems which do.
The following is an example of how to implement a simple dead letter queue for your Service Broker Queue. Note: Many items, such as the schema and message type will all be the same as the ones used by your main queue because both queues will be handling the same messages.
STEP 1: Create a RECEIVE sproc for clearing the queue
This sproc will be used to empty the dead letter queue when you want to reprocess any messages sitting in the queue. This may seem like a lot of SQL for this task, but it will make reprocessing entries in the queue later a simple one line of SQL.
CREATE PROCEDURE [dbo].[fooDeadLetterReceive] AS SET NOCOUNT ON; DECLARE @dh UNIQUEIDENTIFIER; DECLARE @mt SYSNAME; DECLARE @MessageBody XML; BEGIN TRANSACTION; WAITFOR (RECEIVE TOP (1) @dh = conversation_handle, @mt = message_type_name, @MessageBody = message_body FROM [dbo].[fooDeadLetterQueue]), TIMEOUT 1000; WHILE (@dh IS NOT NULL) BEGIN BEGIN TRY --Send message back to the main queue to be processed DECLARE @handle uniqueidentifier; IF (convert(varchar, @MessageBody.query('count(/Root/*)')) != 0) BEGIN BEGIN DIALOG CONVERSATION @handle FROM SERVICE fooService TO SERVICE 'fooService' ON CONTRACT fooMessageContract WITH ENCRYPTION = OFF; SEND ON CONVERSATION @handle MESSAGE TYPE fooMessage (@MessageBody); END CONVERSATION @handle; END END CONVERSATION @dh; COMMIT; END TRY BEGIN CATCH -- If there are any exceptions then end conversation, -- rollback the transaction and stop processing the queue END CONVERSATION @dh; ROLLBACK TRANSACTION; ALTER QUEUE [dbo].[fooDeadLetterQueue] WITH ACTIVATION ( STATUS = OFF ); BEGIN TRANSACTION BREAK END CATCH -- Try to loop once more if there are more messages SELECT @dh = NULL; BEGIN TRANSACTION; WAITFOR (RECEIVE TOP (1) @dh = conversation_handle, @mt = message_type_name, @MessageBody = message_body FROM [dbo].[fooDeadLetterQueue]), TIMEOUT 1000; END COMMIT; --Shutdown processing of the deadletter queue when we have gone through --all existing messages in the queue ALTER QUEUE [dbo].[fooDeadLetterQueue] WITH ACTIVATION ( STATUS = OFF ); GO
STEP 2: Create a SEND sproc for inserting messages into the queue
This sproc will be used later to insert messages into the dead letter queue when the main queue runs into a problem processing a message (Called from the CATCH block of the main queue) .
CREATE PROCEDURE [dbo].[fooDeadLetterSend] @body XML AS DECLARE @handle uniqueidentifier; IF (convert(varchar, @body.query('count(/Root/*)')) != 0) BEGIN BEGIN DIALOG CONVERSATION @handle FROM SERVICE fooDeadLetterService TO SERVICE 'fooDeadLetterService' ON CONTRACT fooMessageContract WITH ENCRYPTION = OFF; SEND ON CONVERSATION @handle MESSAGE TYPE fooMessage (@body); END CONVERSATION @handle; END
STEP 3: Create a Dead Letter queue
Nothing special here, just create a new queue, called fooDeadLetterQueue. Also we attach the activation sproc that will be used for reprocessing of failed messages.
CREATE QUEUE [dbo].[fooDeadLetterQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF , PROCEDURE_NAME = [dbo].[fooDeadLetterReceive] , MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER ) ON [PRIMARY]
Also note that activation on the queue (STATUS=OFF) is currently disabled. This is done on purpose. On a normal queue we always enable activation so that any time a message is received by the queue, it will be immediately processed. In the case of a dead letter queue we do not want messages processed until the messages have been checked/fixed.
STEP 4: Update the Catch block in the receive sproc of your main queue
Normally in the catch block of your TRY CATCH in a service broker queue, if an exception is thrown while processing the message, the catch block is just used as a place to log the error and then turn activation off on the queue so no more messages will be processed.
With your new dead letter queue, rather than turning off the main queue, instead we take the message that failed and send it to the dead letter queue. Now the problem message has been removed from the main queue and we can continue on with processing the rest of the messages in our queue. Now those Systems Administrators can finally relax.
BEGIN CATCH -- If there are any exceptions then end conversation rollback the transaction END CONVERSATION @dh; ROLLBACK TRANSACTION; --Send problem message to the dead letter queue exec dbo.fooDeadLetterSend @MessageBody BEGIN TRANSACTION END CATCH -- Try to loop once more if there are more messages
STEP 5: Reprocessing messages from the dead letter queue
To reprocess the queue you simply have to copy the messages in the dead letter queue back to the main queue. Now that RECEIVE sproc we wrote earlier comes in handy. Simply turn activation on for the dead letter queue. This will take all existing messages on the queue and push them back to the main queue for processing. When the dead letter queue is empty it shuts itself off again.
ALTER QUEUE dbo.fooDeadLetterQueue WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON )