Can't enable a queue with 5 messages

Question

I'm working with SQL Server Express 2012 and I have a Queue.

When I run this:

SELECT TOP 1000 *, casted_message_body = 
CASE message_type_name WHEN 'X' 
  THEN CAST(message_body AS NVARCHAR(MAX)) 
  ELSE message_body 
END 
FROM [TRZIC].[dbo].[AsyncExecQueue] WITH(NOLOCK)

I get five rows.

I made the following script only to empty the queue. But, when I run it:

declare @h uniqueidentifier
        , @messageTypeName sysname
        , @messageBody varbinary(max)
begin try
receive top(1)
    @h = [conversation_handle]
    , @messageTypeName = [message_type_name]
    , @messageBody = [message_body]
    from [AsyncExecQueue];
end try
begin catch
declare @error int
            , @message nvarchar(2048)
            , @xactState smallint;
        select @error = ERROR_NUMBER()
            , @message = ERROR_MESSAGE()
            , @xactState = XACT_STATE();

        raiserror(N'Error: %i, %s', 16, 1, @error, @message);

end catch

I get an error with the following message:

Error: 9617, The service queue "AsyncExecQueue" is currently disabled.

Then I enable the queue on SQL Management Studio, but I refresh SQL Management Studio pressing F5 and then the queue is disabled.

Is there any problem? I want to empty the queue.

Answer 1

If the queue is disabling itself, it's almost always an issue with poison messages. These are messages that the activation sproc cannot process, puts back onto the queue (with a ROLLBACK in a CATCH block), and then continually tries to reprocess. If this happens enough times (5), the queue gets disabled to avoid an endless processing loop.

You should ALTER your queue to enable it and remove the activation procedure ( ...STATUS = ON, ACTIVATION (STATUS = OFF... ) so it won't process automatically, then you can run your manual test.