Delete messages in service broker queue

sql-server queue service-broker
senna picture senna · May 31, 2012 · Viewed 57.3k times · Source

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

Answer

Rebecca Campbell picture Rebecca Campbell · Oct 10, 2013

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

Related questions

SQL Server Process Queue Race Condition

I have an order queue that is accessed by multiple order processors through a stored procedure. Each processor passes in a unique ID which is used to lock the next 20 orders for its own use. The stored procedure then returns …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More