How do I completely reset ServiceBroker including all messages, queues, triggers, etc?

Question

I have a database where I have a ServiceBroker up and running. For unknown reasons we have experienced a lot of problems with missing conversation handlers;

I.e.

The conversation handle "D33EC01C-CEA9-E811-95C5-00155D009F03" is not found.

It is my belief that this has happened when DB has been restarted while conversations have been "open" if that makes any sense.

What I wish to do now is to "reset" the ServiceBroker, remove all conversations, messages, triggers(!) etc.

Unfortunately I haven´t seen any documentation on how to achieve this.

Ideas?

No, ALTER DATABASE db SET NEW_BROKER; will not remove the triggers, queues etc. ONLY messages. — Marcus, Aug 29 '18 at 14:39
I don't know of any way to remove triggers. I don't even know what triggers are in the context of service broker. For the queues you could certainly drop them and re-create them... — Aaron Bertrand, Aug 29 '18 at 14:45

score -1 · Answer 1 · answered Aug 24 '19 at 11:44

-1

Its cause due to multi-trigger on the table which created by service broker.

Delete all trigger and start broker services or then try.

answered Aug 24 '19 at 11:44

virender

99

How do I completely reset ServiceBroker including all messages, queues, triggers, etc?

1 Answers1