CODEWITHSUNDEEP
sql-serverservice-broker
MushinNoShin
MushinNoShin

Reputation: 4886

Drop all messages from queue when status = 0

I have a queue with a number of messages with status = 0. The queue had retention turned on and the conversations were not properly ended. I'd like to clean those messages out.

Receive does not get messages with status = 0, so I can't simply loop over the queue, receiving messages and ending the conversation as I go. Is there any way I can remove these messages without dropping and recreating the queue?

Upvotes: 1

Views: 544

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294307

ALTER QUEUE ... WITH RETENTION = OFF;

Messages with status 0 are messages that were already received and are being retained. This happens because an authorized administrator has enabled retention on the queue, for whatever purposes. See Message Retention:

Messages in a queue that are ready to be received have a status of 1. The RECEIVE statement returns messages that show a status of 1. After the RECEIVE statement returns a message, it sets the status to 0 and leaves the message in the queue if message retention is on.

Upvotes: 1

Related Questions