Disabled queue

Question

I have job, which sends some messages into queue twice a day.
When message came in queue, the SQL external activator runs external application, which reads the message and sends it as report to some users.It works fine couple of days. Than some day i found out that my queue is disabled. And i get messages in transmission queue:

SELECT * FROM sys.transmission_queue with status=5 and message that the message cannot be proceed.

If I clear and enable my queue and clear transmission queue and then send another message into my queue it remains there. No error and nothing in transmission queue. I have to drop all my event notifications, queues, contracts, message types and services and creates them again.

Than it works fine until next time.

How can I found out what is the problem? Why my queue become disabled? Any suggestion?

My code is:

CREATE MESSAGE TYPE [catMessage] AUTHORIZATION [dbo] VALIDATION = NONE

CREATE CONTRACT [catContract] AUTHORIZATION [dbo] ([catMessage] SENT BY INITIATOR)

CREATE QUEUE [dbo].[catInitiatorQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY] 
CREATE QUEUE [dbo].[catTargetQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]
CREATE QUEUE [dbo].[catNotificationQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]

CREATE SERVICE [//changeCatQ/InitiatorService] AUTHORIZATION [dbo] ON QUEUE [dbo].[catInitiatorQueue] ([catContract])
CREATE SERVICE [//changeCatQ/TargetService]  AUTHORIZATION [dbo]  ON QUEUE [dbo].[catTargetQueue] ([catContract])

CREATE SERVICE [catNotificationService]  AUTHORIZATION [dbo]  ON QUEUE [dbo].[catNotificationQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

CREATE EVENT NOTIFICATION catTargetQueueENF ON QUEUE catTargetQueue FOR QUEUE_ACTIVATION TO SERVICE 'catNotificationService', 'current database'

Is there something wrong in my code?

br,Simon

Answer 1

The most common cause of a disabled queue is poison message handling.  By default, a queue is disabled after 5 consecutive rollbacks of the same message in order to prevent an infinate loop.  Note that an explict rollback, or simply not committing an open transaction and closing the connection, will rollback the transaction.  Check your code to ensure transactions are properly committed.

You can enable the queue with ALTER QUEUE.  Assuming it is the target queue:

ALTER QUEUE dbo.catTargetQueue WITH STATUS = ON;

See http://msdn.microsoft.com/en-us/library/ms171592(v=sql.105).aspx for more details on poison message handling and strategies.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

You are right.

When I open my external application it reads messages from my queue:

WAITFOR (RECEIVE TOP(1) conversation_handle, message_type_name, message_body  FROM .....

Than I do something with messages. If error happens(and it did in this case - smtp timeout at sending email) than I catch it and I rollback transaction:

catch (Exception e){
  if (transaction != null)
    transaction.Rollback();

}

Then I write into event log and exit the application. The application should be raised again next time when some new message come into my queue(and that is next day in my case and error would probably gone).
But external activator opens the application again immediately after closing it and error happens again and so on, for times, than queue become disabled and it stops.

Shouldn't external activator open the application only when event notification happens, so when message arrives in catTargetQueue?
It seems that when i rollback transaction, the event is fired again?

One more question here, out of context:
Can I create event notification on top of "//changeCatQ/TargetService" instead of creating new service "catNotificationService" to raise external activator?

br, Simon

Answer 3

Shouldn't external activator open the application only when event notification happens, so when message arrives in catTargetQueue?
It seems that when i rollback transaction, the event is fired again?

A rollback triggers another notification event.  Otherwise, the application wouldn't get launched again after a rollback.

There a number of ways to address this problem.  If you are running SQL Server 2008 R2 or later, you can disable poison message handling on the queue and add code to alert and retry (after a delay).  Or you could instead write the message to separate table or queue after an error and commit, alowing messages to be reprocessed later via a manual process.

One more question here, out of context:
Can I create event notification on top of "//changeCatQ/TargetService" instead of creating new service "catNotificationService" to raise external activator?

You will need a separate service/queue in order to to deliver notifications expected by the EA service, However, you don't necessarily need to rely on a queue activation notification event.  The EA service just receives messages on the queue associated with the specified notfication service, expecting type [http://schemas.microsoft.com/SQL/Notifications/EventNotification].  When the message is received, it looks up the application program in the config file that matches the ServerName, DatabaseName, SchemaName and ObjectName contained in the event message.  So you could create your own message to launch the application using the technique detailed by Davide Mauri at http://sqlblog.com/blogs/davide_mauri/archive/2011/02/07/forcing-an-external-activation-with-service-broker.aspx

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

"A rollback triggers another notification event.  Otherwise, the application wouldn't get launched again after a rollback."

It would be launched again when next message arrives - that is perfectly ok. At every launch it collets all messages from queue.So, if I disable the poison message on queue and rollback transaction, than notification wont be triggered because of rollbacked transaction(only on next message arrivals)? Otherwise there is no way to say that notification should be triggered only on message arrivals?

One more question. 

After error, when I enabled queue again and clear all messages from it(there is no message in transmission_queue or catTargetQueue or catNotificationQueue).

Why exetrnal activator is not started when new message came into queue? It should started? I can't find the reason. Any idea?
(only if i drop everything and recreate again, than it work again).
I send new message to queue like this:

BEGIN TRANSACTION;

	DECLARE @InitiatorDialogHandle UNIQUEIDENTIFIER;
	DECLARE @RequestMessage XML;
	
	SELECT @RequestMessage =(
	SELECT (
	SELECT 'test'AS test
	FOR XML RAW('Stock_difference'), TYPE)
	FOR XML RAW('Root'));
	
	IF( @RequestMessage.value('count(/Root/*)','int'))>0
	BEGIN
		BEGIN DIALOG @InitiatorDialogHandle
		 FROM SERVICE [//changeCatQ/InitiatorService]
		 TO SERVICE
		  N'//changeCatQ/TargetService'
		 ON CONTRACT
		  [catContract]
		 WITH ENCRYPTION = OFF;

		 SEND ON CONVERSATION @InitiatorDialogHandle
		 MESSAGE TYPE [catMessage](@RequestMessage);
	END
	
COMMIT TRANSACTION;

The message remains in catTargetQueue and nothing happens.

Thank you very much for your help,

Simon

Answer 5

So, if I disable the poison message on queue and rollback transaction, than notification wont be triggered because of rollbacked transaction(only on next message arrivals)? Otherwise there is no way to say that notification should be triggered only on message arrivals?

Both the receive and event notification are rolled back when your code issues the rollback.  So the same event occurs again regardless of the poison message handling status on the queue.

Why exetrnal activator is not started when new message came into queue? It should started? I can't find the reason. Any idea?
(only if i drop everything and recreate again, than it work again).

Mostly likely, the queue monitor associated with the event notification was dropped due to the rollbacks and queue disabling.  In that case, just recreate thje EVENT NOTIFICATION using a script like the one below.  No need to recreate everything.

IF NOT EXISTS(
	SELECT *
	FROM sys.dm_broker_queue_monitors qm
	JOIN sys.service_queues sq ON sq.object_id = qm.queue_id
	WHERE 
		database_id = DB_ID()
		AND OBJECT_NAME(queue_id) = N'dbo.catTargetQueue')
BEGIN
	DROP EVENT NOTIFICATION catTargetQueueENF
		  ON QUEUE dbo.catTargetQueue;
	CREATE EVENT NOTIFICATION catTargetQueueENF 
		ON QUEUE dbo.catTargetQueue 
		FOR QUEUE_ACTIVATION 
		TO SERVICE 'catNotificationService', 'current database';
END;
GO

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

You are right, it is missing. What is then the point of disabling the poison message from queue?

Is it possible to END CONVERSATION (without WITH CLEANUP) on error, then the message would be sent to Initiator queue and then commit transaction.
And the system will probably work for future messages without disabling target queues and stop working?
And after I can see all non delivered messages in Initiator queue and clean them(because queue has probably some limit).

But probably the best way is to commit transaction on error and write message to some outer table.

Do you have some good article about service broker besides MSDN?

Thanks,
Simon

Answer 7

What is then the point of disabling the poison message from queue?

The purpose of the default poison message handling (queue disable) is to prevent an infintate loop due to a bad message or other situation that can't be corrected without manual intervention.  In some cases (e.g. intermitent SMTP server error), you can simply turn off poison message handling and retry later (after perhaps waiting a few minutes).  But you need to be careful such that only those messages that can be successfully processed later are retried.  For example, if you have a malformed email address, the email will err on every attempt.  In that case, you should write the bad data to an error table rather than retry.

I must admit is a challenge to develop resiliant asynchronous applcations because it is difficult to anticipate every conceivable error and take the appropripriate action.  I suggest you develop a whitelist of "expected" errors that can be retried and just write others to an error table or queue.  Also, implement monitoring so that you are alerted of errors like disabled queues, excessive errors, etc.

Is it possible to END CONVERSATION (without WITH CLEANUP) on error, then the message would be sent to Initiator queue and then commit transaction.
And the system will probably work for future messages without disabling target queues and stop working?
And after I can see all non delivered messages in Initiator queue and clean them(because queue has probably some limit).

But probably the best way is to commit transaction on error and write message to some outer table.

As long as you don't have a conversation that requires messages be processed (successfully) in order, suspending the message(s) for later processing will work.  This can be done by either the target or initiator (in response to EndDialog or your custom message).  I personally don't feel stronly one way or the other.  In ether case, you are only limited by storage space.

Do you have some good article about service broker besides MSDN?

One of my favorate online articles is the classic Rasanu article on the fire-and-forget anti-pattern:  http://rusanu.com/2006/04/06/fire-and-forget-good-for-the-military-but-not-for-service-broker-conversations/.  Although it was written for SQL 2005, the concepts are important and still apply. 

There are a couple of books you might consider, Pro SQL Server 2008 Service Broker (http://www.amazon.com/Server-Service-Broker-Books-Professionals/dp/1590599993) and The Rational Guide to SQL Server 2005 Service Broker (http://www.amazon.com/Rational-Server-Service-Broker-Guides/dp/1932577270).  I haven't had a chance to read the latter but it's been recommended to me by experts whose opinion I respect.

The good thing about Service Broker is that is very flexible and extensible.  The bad thing about Service Broker is that it is very flexible and extensible, much like building with Legos :-)

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 8

A very good way to avoid poison messages is another construct also presented by Rusanu: upon receipt of the message you first insert it into a table designated for the purpose (i.e. something like dbo.Processing, a table that only contains messages that are currently being processed) and then commit the transaction. Do not end the conversation yet! After you have committed the transaction, you start a new transaction, delete the entry from the dbo.Processing table and process the message as required for your functionality (send a mail in your case). If the processing succeeds, you end the conversation as normal and commit. If anything fails -you loose your connection, the smtp process times out, your application crashes or you simply rollback the transaction- the message will still be in the table for you to analyze or retry, but the queue will not be disabled and future messages can continue to be processed as normal.

In a more advanced schema you can extend this by using Service Broker's timer mechanism to create an automatic retry: while still inside the initial transaction start a timer to go off in 86400 seconds = 24 hours (= your retry period). Now, for any message that was not successfully sent (i.e. the conversation was not ended yet), you will receive 24 hours after the message was first delivered, a DialogTimer message. In the transaction that received the DialogTimer message, you update the dbo.Processing message to increment a retry counter, start a new timer to go off in 86400 seconds and commit the transaction. Then you start a new transaction, delete the entry from the dbo.Processing table and retry sending the message. If it succeeds, you end the conversation and commit the transaction. If it doesn't, the 2nd transaction is rolled back again and the message remains in the table and you can either have a look at what went wrong or the timer fires again 24 hours later to start another retry. All the time, any other messages will still continue to be processed.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.