Go to content Go to navigation Go to search

Brokenwire.NET::Programming

SQL Server Service Broker: cleanup
· 2008-06-05 09:26 by Thijs Kroesbergen for Brokenwire.NET

Imagine that you've been playing with the SQL Server Service broker, and you've got thousands of conversations stuck in the queue. And then you discover that there is nothing like "truncate table" possible on the queue... You can cleanup each conversation in the queue with the "end conversation with cleanup" statement.

So, a bit of searching and this is the result:

WARNING: DO NOT USE THIS ON A PRODUCTION ENVIRONMENT: the messages in the queue are lost forever!

use [YOURDB]
declare @handle uniqueidentifier

declare conv cursor for 
  select conversation_handle from sys.conversation_endpoints

open conv
fetch next from conv into @handle

while @@FETCH_STATUS = 0
 Begin
   END Conversation @handle with cleanup
   fetch next from conv into @handle
 End

close conv
deallocate conv

To see how many conversations there a left:

select count(*) from sys.transmission_queue

Have fun! (I know I did)
More about the Service Broker and ending conversations soon on this channel.

UPDATE:

The really really quick 'n dirty way:

ALTER DATABASE LogistiekeMeetpuntenAdministratie WITH NEW_BROKER

Permalink -