Delete sys.Transmission_Queue

Question

I have seen this buried deep with the questions on Service Broker, but I am looking for it again.  How do you delete all records from your sys.Transmission_Queue.  This is on a test server and I want to clean it before some more test.

 

 

Answer 1

alter database foo set new_broker;

Answer 2

is this going to create a new broker or clear the messages waiting in trasmissionqueue?

Answer 3

Hi

 

I think you can do END CONVERSATION <conversation_handle> WITH CLEANUP for each of your conversations which will clean up the transmission queue.

 

Ben

Answer 4

We have a problem with one of our test servers. The CPU is going up to %100 becuase of service broker activation messages. We have found out that there around 2 milllion messages in the end point and over 350000 messages in transmission queue. I have been running the below scripts and only managed to delete a little bit more than the quarter of the messages in both tables. I understand that the way it works is, any messages coming from another database firstly goes to endpoint and then to transmission point. As this is a test server and we dont need any of the messages, we would like to get rid of all. Is there any quicker way of doing this,  as clearing it all up is going to take us weeks.

--clear up endpoints

declare
@conversationHandle uniqueidentifier
select
top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints
while
@@rowcount = 1
begin
end conversation @conversationHandle with cleanup
select top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints
end

--clear up tramissiong_queue

DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT conversation_handle FROM sys.transmission_queue
OPEN item_cursor
DECLARE @conversation UNIQUEIDENTIFIER
FETCH NEXT FROM item_cursor INTO @conversation
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT 'Closing conversation: ' + CAST(@conversation AS VARCHAR(50))
    END CONVERSATION @conversation WITH CLEANUP
    FETCH NEXT FROM item_cursor INTO @conversation
END

Answer 5

try this

declare @conversation uniqueidentifier

while exists (select 1 from sys.transmission_queue )

begin

set @conversation = (select top 1 conversation_handle from sys.transmission_queue )

end conversation @conversation with cleanup

end