I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer.
When I setup event notification on a queue, it creates a queue monitor and if receiving a message from that queue causes a poison message the queue monitor disappears. It's not in "RECEIVE" or "INACTIVE" it's just drops. And after enabling the queue with ALTER QUEUE somequeue WITH STATUS = ON; the queue monitor does not re-appear and and I need to drop and recreate event notification in order to have event notification working again.
So the question is, is that by design or not? And after I re-enable the queue after poison message is it a common practice to recreate event notifications.
Thanks!
Best Answer
When a transaction rolls back five times, Service Broker disables all queues that the transaction received messages from, even if you have poison message handling turned off. This would mean recreating the event notifications. So, adopt try .. catch and handle your poison messages. You get four tries before SB will handle it for you.