I encountered a weird SSB 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'.
So 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 ?
Aucun commentaire:
Enregistrer un commentaire