Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

My initiator queue is filled with about 18 million old messages of either EndDialog or Error message_type_name.

Apparently the Microsoft sample code I used to set up internal activation (http://msdn.microsoft.com/en-us/library/cc281517.aspx) didn't include a step for ending the dialog on the Initiator side (I've read that this is called "Fire and Forget").

I'm wondering, because my sysdercv and other service broker related tables are now into the double digit gigabytes: how can I get rid of these old messages?

I've tried to run END CONVERSATION on several thousand so far, but I'm not seeing any change in size in those system tables.

Additionally, how can I alter the Initiator Service/Queue to automatically EndDialog in the future?

Edit: I should mention that despite more and more messages passing through the pipeline, I haven't seen any of these system tables change size in at least a half hour.

share|improve this question

1 Answer

up vote 0 down vote accepted

Actually, the tutorial you mention does issue END CONVERSATION from both ends. It therefore doesn't suffer from the "Fire and Forget" pattern. However, the first END CONVERSATION (initiator side) is issued manually from a query window, which is not very practical. In production you'll probably want to have an activated procedure on both ends - the one on the initiator side would receive a reply from the target and end the conversation (that is if you want to keep to the message exchange scheme from the tutorial).

share|improve this answer
So if I run something like declare @handle uniqueidentifier declare @N int set @N = 1 while @N <= 1000 begin set @handle = (select top 1 conversation_handle from atomicchangesinitiatorqueue with(nolock) where message_type_name = 'schemas.microsoft.com/SQL/ServiceBroker/EndDialog'; or message_type_name = 'schemas.microsoft.com/SQL/ServiceBroker/Error';) END CONVERSATION @handle set @N = @N + 1 END And loop through that on some fixed schedule (i.e. deleting top 1000 at a time) should I expect to see these system tables shrink in size? – Aushin Feb 14 '11 at 19:22
Ugh I apologize for the format of that comment, can't seem to make newlines. – Aushin Feb 14 '11 at 19:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.