3

I encountered a weird SQL Server Service Broker consistency problem, namely I have a queue that holds messages (status=1, Ready to receive) which are lying there for a very long time.

In the meantime there was an upgrade from SQL 2005 to SQL 2012 and now when I want to do something with these messages (e.g. END CONVERSATION, even with CLEANUP) I get an error:

The conversation handle "......." is not found.

So I tried to look for this handle in sys.conversation_endpoints, but to my surprise it's not there. This shouldn't have happened, as far as I know if there are messages in the queue with associated conversation_group_id and conversation_handle, there should be an endpoint for each one.

I ended up running DBCC CHECKDB and got messages such as:

Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 9708, State 1: The messages in the queue with ID some_number are referencing the invalid conversation group 'some_guid'.

Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 9705, State 1: The messages in the queue with ID some_number are referencing the invalid conversation handle 'some_guid'.

My question is: how could this happen? Are there any potential DEV/DBA errors or gotchas e.g. while performing SQL upgrade, that might lead to such corruption?

Improve this question
5
  • Did you read through this article on upgrading to SQL Server 2012? In particular the DBCC CHECKDB WITH DATA_PURITY; step?
    – TT.
    Jan 25, 2016 at 19:20
  • Also, are you able to observe that there are messages in the queue that don't have a corresponding row in sys.conversation_endpoints?
    – Ben Thul
    Jan 25, 2016 at 21:35
  • @TT. Yes, I've seen LaRock's article but the step you mentioned is in its majority relevant to databases created in SQL 2000, but my database was created in SQL 2005. Now the database is in compatibility_level = 110 and besides the queue in question has messages that have corresponding rows in sys.conversation_endpoints, so there are only a few messages that are left without endpoints and fail CHECKDB checks.
    – LSZ
    Jan 26, 2016 at 8:58
  • @BenThul That is exactly the problem which I described in the issue :)
    – LSZ
    Jan 26, 2016 at 9:04
  • @LSZ: I was more curious about the other side of the conversation, but sure. Either way, do an 'end conversation <handle> with cleanup' and monitor for it in the future.
    – Ben Thul
    Jan 26, 2016 at 15:30

1 Answer 1

Reset to default
1

Corruptions can occur due to media corruption or memory bit-flips. Ideally, one has a backup and restores from a backup.

If you cannot restore from a uncorrupted DB, when you have a situation where you have inconsistencies between internal tables you can try to run REPAIR_ALLOW_DATA_LOSS. If repair does not fix the issues you can force a new broker in the database (ALTER DATABASE ... SET NEW_BROKER) which will wipe clean all conversations and messages. You will loose all messages pending processing. You need to exercise care if you have remote conversations (endpoints in different server or even different DB) as the partner endpoint will be left stranded and will have to be cleaned up. NEW_BROKER changes the service_broker_guid in sys.databases and this may impact routes.

If you need more detailed help you'll have to open a case with product support.

Are there any potential DEV/DBA errors or gotchas e.g. while performing SQL upgrade, that might lead to such corruption?

I'm not aware of any, I suspect more likely to be flaky media/memory/cpu (they all can cause corruptions).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.