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

回答1:

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

回答2:

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

回答3:

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

End Converstation @c With CleanUp

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

回答4:

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

回答5:

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

        if(@@ROWCOUNT = 0) break;
end