Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Jul 20 '12 at 1:50

This question came from our site for professional and enthusiast programmers.

    
I don't quite remember how it works, but when poison messaging handling happens and the queue gets disabled, is activation also disabled? If so, when you reenable the queue, does activation get re-enabled? I could see where it wouldn't. Check in sys.service_queues after re-enabling the queue to see if activation is enabled. If not, enable it and then see if your monitor shows up. –  Ben Thul Jul 20 '12 at 1:07
    
when I re-enable the queue the queue monitor does not get re-enabled. This is an event notification not internal activation, in sys.services_queues you see if activation is enabled on internal activation. –  Sergey Jul 20 '12 at 1:10
    
I guess we both are sending notification to some other queue by using event notifications. The difference is that you are processing these messages by yourself, while i delegate it to SSBS external activator service, developed by Microsoft. Am i right? (stackoverflow.com/questions/10299870/…) –  Jānis Jul 20 '12 at 7:34
    
That you have a queue monitor means that you have activation somewhere, whether that be automatically created by the query notification or otherwise. Check the is_activation_enabled bit on all of the queues in your "good" state and then after your poison message handler disables your queue and compare. I suspect that there'll be a difference. –  Ben Thul Jul 20 '12 at 15:35
    
Janis, I'm also using external activation. –  Sergey Jul 20 '12 at 16:27

1 Answer 1

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.

share|improve this answer

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.