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?
DBCC CHECKDB WITH DATA_PURITY;
step?