Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Simulate message poisoning in Service Broker Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 1:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:13 PM
Points: 27, Visits: 141
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
Post #1562841
Posted Friday, April 18, 2014 10:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:33 PM
Points: 7,042, Visits: 12,970
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1563105
Posted Friday, April 18, 2014 5:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:13 PM
Points: 27, Visits: 141
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.
Post #1563204
Posted Monday, April 28, 2014 1:20 PM This worked for the OP Answer marked as solution
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:13 PM
Points: 27, Visits: 141
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 '&lt;conversation_handle&gt;' WITH CLEANUP

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

When done, re-enable the queue with the following SQL.
ALTER QUEUE &lt;queue name&gt; WITH ACTIVATION (STATUS = ON)

Post #1565686
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse