SqlBroker clean up

Testing SqlBroker for the 1st time outside the development environment I realized I did not define the Authorization attribute when creating the objects (messagetype, contract & services) see Asynchronous triggers using SQL Broker for more details.

This mistake filled the queue with over 300,000 messages that were not going anywhere even after I fixed the Authorization.

Good thing I found this on test environment which allowed me to run a short script that cleans up those messages without worrying about the consequences.

BTW, if you were thinking of dropping the objects and rebuild them...tried that - got a weird out of memory message from SQL Server.

Here is the script:

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

Till next time...
Diego