Is there a way to 'peek' at messages for a Disabled SQL Broker Service message queue?

92 views Asked by At

I'm looking at a legacy process for something I'm not 100% familiar with - SQL service broker queues. We had a poison message shut off the queue for a few weeks now. In the past when we have had to enable this queue again, it processes messages that have been waiting since the day it was last disabled and I would like to see/'peek' at them before it sends them through.

I've checked the sys.transmission_queue and it's empty currently.

Running this snippet of code also throws an error that the queue is disabled. This SQL snippet is from the stored procedure that the queue is mapped to. When enabled, the messages come through just fine.

WAITFOR (
            RECEIVE TOP(1)
                @conversation = conversation_handle,
                @messagetypename = message_type_name,
                @messagebody = CAST(message_body AS XML)
            FROM ApplicationDispatchQueue
        )

Any advice on if I can see the pending messages would be appreciated, thank you!

Heres the SQL snippet of my created queue. The service broker is mapped to the exact same queue and database.

USE [IRIS]
GO

/****** Object:  ServiceQueue [ApplicationDispatchQueue]    Script Date: 7/11/2023 1:13:02 PM ******/
CREATE QUEUE [dbo].[ApplicationDispatchQueue] WITH STATUS = OFF , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[DataEventDispatchRequestProcessor_sp] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo'  ), POISON_MESSAGE_HANDLING (STATUS = ON)  ON [IRIS_Data_01] 
GO

The select * from sys.conversation_groups returns about 2103 records, all similar to this

conversation_group_id   service_id  is_system
C03A7023-521C-EE11-A2C8-00505681A00F    65538   0

and heres the record for the relevant service queue. There are 9 total queues but only one is named ApplicationDispatchQueue

ApplicationDispatchQueue    52103872    NULL    1   0   SQ  SERVICE_QUEUE   2014-07-24 21:04:44.800 2023-06-21 15:51:38.593 0   0   0   1   [dbo].[DataEventDispatchRequestProcessor_sp]    1   1   0   0   0   1
0

There are 0 answers