CODEWITHSUNDEEP
sql-serverqueueservice-broker
senna
senna

Reputation: 329

Delete messages in service broker queue

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).

Upvotes: 24

Views: 75876

Answers (6)

Jānis
Jānis

Reputation: 2266

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.

Upvotes: 10

kenneth
kenneth

Reputation: 21

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

Upvotes: 2

Oscar Acevedo
Oscar Acevedo

Reputation: 1212

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

Upvotes: 2

Ben Thul
Ben Thul

Reputation: 32707

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

Upvotes: 26

rewriter
rewriter

Reputation: 1

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

        if(@@ROWCOUNT = 0) break;
end

Upvotes: -2

Rebecca Campbell
Rebecca Campbell

Reputation: 835

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

Upvotes: 58

Related Questions