April 17, 2014 at 1:49 pm
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
April 18, 2014 at 10:26 am
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...
April 18, 2014 at 5:10 pm
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.
April 28, 2014 at 1:20 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply