Simulate message poisoning in Service Broker

  • grantbanjo

    Ten Centuries

    Points: 1136

    Hello,

    I have set up Service Broker in a test database running on SQL Server 2008 R2. Within this service broker I have applied the steps defined in the following link to send an email alert when message poisoning takes place (five consecutive transaction rollbacks to a queue producing a "Broker:Queue disabled" event).

    http://blog.maskalik.com/sql-server-service-broker/setup-disabled-queues-email-notification/

    In order to test this I need to simulate message poisoning. As I am new to Service Broker, can somebody provide the steps of simulating message poisoning with five consecutive rollbacks of a transaction to a queue?

    Thanks,

    Grant

  • LutzM

    SSC Guru

    Points: 107049

    A poisoned message is a message leading to an error because of the message itself and not a database condition.

    For instance, a message leading to a deadlock is not a poisoned message because it can complete with the next retry.

    A message leading to a primary key violation will always fail, regardless of the number of retries (assuming there's no DELETE operation in between...).

    To test such a scenario, find a message condition that'll pass your validation process (I hope, there's such a code section 😉 ) but will still fail, leading to a rollback of the code activated by the message.

    Without knowing the tasks you trigger with a SSSB message it's hard to tell how to simulate a poisoned message...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • grantbanjo

    Ten Centuries

    Points: 1136

    Thanks for your help. I was overthinking things and realized that all I had to do was temporarily apply a "SELECT 1/0" in my activation stored procedure and that did the trick... along with applying a RAISERROR.

  • grantbanjo

    Ten Centuries

    Points: 1136

    As a follow up, I found that applying the RAISERROR will create a message in the queue with a status = 1, but does not automatically disable the queue, as will take place with actual message poisoning (following five consecutive rollbacks of a transaction). Since duplicating this with a problem transaction can be tricky from one system to another, the best approach is to just send a message and force five rollbacks with the following SQL (applying your own queue name in place of the <> brackets).

    DECLARE @ch UNIQUEIDENTIFIER

    DECLARE @messagetypename NVARCHAR(256)

    DECLARE @messagebody XML

    WHILE (1=1)

    BEGIN

    BEGIN TRANSACTION

    WAITFOR (

    RECEIVE TOP (1)

    @ch = conversation_handle,

    @messagetypename = message_type_name,

    @messagebody = CAST(message_body AS XML)

    FROM <queue name> --TargetQueue

    ), TIMEOUT 60000

    IF (@@ROWCOUNT = 0)

    BEGIN

    ROLLBACK TRANSACTION

    BREAK

    END

    -- Rolling back the current transaction

    PRINT 'Rollback the current transaction - simulating a poison message...'

    ROLLBACK TRANSACTION

    END

    GO

    In the Message tab below this should display five lines beginning with "Rollback" and a final line stating that queue is currently disabled. This will take around 30 seconds to return.

    To clean up the queue, find each unique conversation_handle in the queue with a status = 1 and manually apply one at a time in the following SQL. For the above code, there should only be one unique conversation handle.

    Format:

    END CONVERSATION '<conversation_handle>' WITH CLEANUP

    Example:

    END CONVERSATION '28D53504-42CB-E311-9E82-B8CA3A5EE06C' WITH CLEANUP

    When done, re-enable the queue with the following SQL.

    ALTER QUEUE <queue name> WITH ACTIVATION (STATUS = ON)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply