This is the fourth installment in the 12 day series for SQL tidbits during this holiday season.
Previous articles in this mini-series on quick tidbits:
Brokers
On a recent opportunity to restore a database for a client, I experienced something new.
I thought it was intriguing and it immediately prompted some questions. First, let’s take a look at the message that popped up during the restore and then on to what was done to resolve the problem.
Query notification delivery could not send message on dialog ‘{someguid}.’. Delivery failed for notification ‘anotherguid;andanotherguid‘ because of the following error in service broker: ‘The conversation handle “someguid? is not found.’
My initial reaction was “Is Service Broker enabled?” The task should have been a relatively easy straight forward database restore and then to setup replication after that. My next question that popped up was “Is SB necessary?”
Well the answers that came back were “Yes” and “YES!!!” Apparently without SB, the application would break in epic fashion. That is certainly not something that I want to do. There are enough broke brokers and broke applications without me adding to the list.
Occasionally when this problem arises it means that the Service Broker needs a “reset.” And in this case it makes a lot of sense. I had just restored the database and there would be conversations that were no longer valid. Those should be ended and the service broker “reset.”
The “reset” is rather simple. First a word of warning – do not run this on your production instance or any instance without an understanding that you are resetting SB and it could be that conversations get hosed.
ALTER DATABASE <dbname> SET NEW_BROKER WITH ROLLBACK IMMEDIATEFor me, this worked like a charm. There was also substantial reason to proceed with it. If you encounter this message, this is something you may want to research and determine if it is an appropriate thing to do.
