Delete messages in service broker queue

Question

I'd like to clear my queue in SQL Server Management Studio, but I don't want to delete the whole queue just the content in the queue (the messages).

Do you want to clear all messages in all the queues in a database? The you should use `ALTER DATABASE ... SET NEW_BROKER WITH ROLLBACK IMMEDIATE;` — Remus Rusanu, Jun 01 '12 at 05:05

score 53 · Answer 1 · answered Oct 10 '13 at 19:50

53

Just combining the two previous answers (by Ben and Jānis) for clarity. This worked for me:

declare @c uniqueidentifier
while(1=1)
begin
    select top 1 @c = conversation_handle from dbo.queuename
    if (@@ROWCOUNT = 0)
    break
    end conversation @c with cleanup
end

answered Oct 10 '13 at 19:50

Rebecca Campbell

775
1
6
14

1

Another good thing with this answer is that using `SELECT` instead of `RECEIVE` makes it possible to cleanup the queue even if it's disabled. – jgauffin Feb 27 '15 at 08:04
2

Worked for me when I changed the (@@ROWCOUNT=0) check with a (@c is null) condition. Thanks – earthling42 May 15 '15 at 11:27

score 25 · Accepted Answer · edited Dec 10 '18 at 15:45

25

Something like this should work:

while(1=1)
begin
    waitfor (
        receive top(1)
        conversation_group_id
        from dbo.yourQueue
    ), timeout 1000;

    if (@@rowcount = 0)
        break;
end

edited Dec 10 '18 at 15:45

Joey Adams

41,996
18
86
115

answered May 31 '12 at 14:35

Ben Thul

31,080
4
45
68

If you are using a [messaging pattern where the receiving endpoint cleans up the conversation](http://rusanu.com/2014/03/31/how-to-prevent-conversation-endpoint-leaks/), you may need to give special treatment to certain received messages (e.g. call END CONVERSATION on any messages whose type is EndOfStream). – Joey Adams Jul 25 '16 at 17:18

score 10 · Answer 3 · edited Jun 02 '22 at 22:54

10

I would use end conversation (that will also remove all related messages from all queues) using statement:

End Conversation @c With CleanUp

if you just receive message, then you leave conversation open. End Conversation With CleanUp is for specific situations only.

edited Jun 02 '22 at 22:54

MotKohn

3,485
1
24
41

answered Jun 04 '12 at 14:30

Jānis

2,216
1
17
27

score 2 · Answer 4 · edited Oct 27 '19 at 07:45

2

If you are using SQL Server (starting with 2008) you can use RECEIVE

WHILE (0=0)
BEGIN
    RECEIVE * FROM dbo.YourQueue;
    IF (@@ROWCOUNT = 0) BREAK;
END

edited Oct 27 '19 at 07:45

Dale K

25,246
15
42
71

answered Oct 31 '17 at 17:08

Oscar Acevedo

1,144
1
12
19

score 1 · Answer 5 · answered Nov 08 '21 at 21:22

I had the same issue as the original poster, but I was needing to clear queues with millions of messages (failed message queues, especially in non production environments that had not been checked for years).

The solution above would have worked, but was processing at less than 10 messages per minute. By doing it in batches I was getting 30000 messages per minute.

The only noteworthy item in the code is where validation = 'N'. This limits the conversation handles to the real messages. There is a duplicate conversation handle for the response/error which gets removed by the end conversation. Without this clause the script would still work, but generate a lot of errors in the output.

declare @conversationBatch table (convH uniqueidentifier)
declare @conversationHandle uniqueidentifier

declare convCursor cursor for
    select convH from @conversationBatch

insert into @conversationBatch
    select top 1000 conversation_handle  
      from dbo.queuename WITH (NOLOCK) 
     where validation = 'N'


while @@rowcount > 0
begin
    open convCursor
    fetch next from convCursor into @conversationHandle
    while @@FETCH_STATUS = 0 
    begin
        end conversation @conversationHandle with cleanup
        fetch next from convCursor into @conversationHandle
    end
    close convCursor

    delete from @conversationBatch
    insert into @conversationBatch
        select top 1000 conversation_handle  
          from dbo.queuename WITH (NOLOCK) 
         where validation = 'N'
    
 end

 deallocate convCursor

score -2 · Answer 6 · edited Dec 10 '15 at 12:34

-2

while(1=1)
begin
    waitfor (
        receive top(1)
        conversation_group_id
        from kartokumaqueue2), timeout 1000;

        if(@@ROWCOUNT = 0) break;
end

edited Dec 10 '15 at 12:34

Jonnus

2,988
2
24
33

answered Dec 10 '15 at 12:14

rewriter

1

3

This answer while potentially correct lacks context and would be improved by a code commentary stating why this answer works. See http://stackoverflow.com/help/how-to-answer – Jonnus Dec 10 '15 at 12:18

Delete messages in service broker queue

6 Answers6

Linked