Query Notifications filling up the transmission queue

I have a web application that sets up a query notification on a queue. Normal operation works fine, but for some reason the queue being monitored disables occasionally (even with the transaction, error message, and end dialog message) pattern set up as described in Remus' blog post. When the queue disables, everything goes insane and the transmission queue, left unchecked, fills up the transmission queue with hundreds of thousands of PostQueryNotification messages. Am I doing something wrong? What should I do to mitigate this because it IS possible that the queue might disable sometimes, and I don't want drive space eaten up on a low-end SQL Express machine, if it does. Also - second question - where is the transmission queue storing all these messages? TempDB?

I still haven't found a solution to this problem - even after applying normal poison message handling within the .NET code. Anyone? What to do about these query notifications filling up the xmit queue (and therefore the disk) when the queue disables?|||

One more question with regard to this behavior:

Why does service broker start a new conversation for each one of these notifications? This seems to compound the problem by not reusing a valid conversation_handle. I hope someone can provide an answer or solution to this soon because its close to holding up an intended launch at this point. Basically, I need to know what to do when a queue becomes disabled that is being monitored by a .NET application for query notifications. It seems like maybe I have to set up the architecture to receive the event notification in the .NET application and try to recover from code, rather than receiving it in SQL Server and recovering, which is what I would rather do.

|||

Even if it would reuse the conversations, the same number of notifications (messages) would be fired, so the transmission_queue would grow to the same size.

1) Why does the queue get disabled in the first place? In a development environment this occurs more often than normal because developers tend to debug applications and abruptly intrerupt execution, thus causing rollbacks that eventually disable the queue, but in a normal/test environment this should be a rare event. The fact that the queue gets disabled too often might indicate an application problem.

2) How many notifications are fired that the rate of growth for transmission queue is a issue of concern? Normally applications subscribe to a notification and then re-post the query when they are notified. So there should be a natural limit on the number of notifications fired and not delivered, since applications would not post new queries until the previous ones are notified. From your description it seems that new quesries are posted continuosly, which would indicate a problem in the application.

3) How did you set up and process the queue_diabled notification? Why did you had problems with this?

|||

Well, here is the setup of the notification, started on a separate thread from the main thread of the web application:

using (_command =

new SqlCommand("usp_ProcessMessage", _connection))

{

_command.CommandType = CommandType.StoredProcedure;

_command.CommandTimeout = 0;

string service = "service=myService" ;

_sqlNotificationRequest = new SqlNotificationRequest(new Guid().ToString(), service, 0);

_command.Notification = _sqlNotificationRequest;

while (true)

{

...

// BeginTransaction

// ExecuteReader

// Do Processing on Message

...

}

}

The stored procedure looks like this:

CREATE PROCEDURE [dbo].[usp_ProcessMessage]

AS

BEGIN

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

FROM [tcp://MyQueue] ) ;

END

When [tcp://MyQueue] becomes disabled, the problems start. The question as to why the queue is becoming disabled is still under investigation and, I agree that this happens more in development. But, I am seeing it regularly, even with measures in place to dequeue the message and try to recover. In any case, even if I can isolate what is causing the queue to disable, I still need to have a recovery model in place - JUST IN CASE. So, is there something intrinsically wrong with the code above? Should I be doing something else? The number of PostQueryNotifications is definitely unmanageable when the queue disables....let me put it this way: they are filling up the sys.conversation_endpoints and sys.transmission_queue faster than I can end and delete them gracefully. A count yielded somewhere between 800,000 and 1M generated in the last 12 hours.

|||I don't really understand your code. The SqlNotificationRequest should be attached to a SqlCommand that retrieves the data to be notified from an user table (eg. a SELECT) and the RECEIVe statement should get the notifications when the content of the SELECT changes.
Your code attaches the SqlNotificationRequest to a SqlCommand that invokes the stored procedure containing the RECEIVE. I guess you get back immedeatly the notification that the subscription query is incorrect, not an actual notification.|||

Now I don't understand. I am trying to get an application notification whenever a message arrives in a queue. When I get the notification that something is there (in the queue), I want to RECEIVE and process the message(s). So can you help me understand how the commands are to be structured to achieve that?

Thanks again.

|||

Simply issue a SqlCommand.ExecuteReader() with the statement "WAITFOR (RECEIVE ...)" without setting up any SqlNotificationRequest. The RECEIVE statement will block until a message is available to be dequeued and processed by the application. Query Notifications is a generic mechanism for detecting changes on any data table, it does not work with message queues.

|||

OK, so can you explain what is happening in the code that I submitted? By attaching that notification to the command object that is calling the stored procedure with the WAITFOR(RECEIVE...), I am getting notifications for what? Or is it that I am simply getting a bunch of errors from the notification framework, as you mentioned?

If I understand you correctly, I don't need query notifications at all - just call that stored procedure normally; its on a different thread so it will block, but not the application, until a message arrives, at which time, the reader will RECEIVE and process the message? Is that correct?

|||

You should always check the XML payload of the received notifications and see wether is an actual notification or an error emssage. In your case you probably get similar to this: <qn:QueryNotification mlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="0" type="subscribe" source="statement" info="invalid" database_id="0" sid="..."><qn:Message>00000000-0000-0000-0000-000000000000</qn:Message>
</qn:QueryNotification>
The info="invalid" is the attribute that tells you that the submited query is not a valid one for notifications.