Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
psharpebr
Starting Member
2 Posts |
 Posted - 2013-03-22 : 15:13:57
|
| Hi Team,I am getting this message after running DBCC CHECKDB,exactly this commandDBCC CHECKDB (MyDatabase) WITH all_errormsgs, no_infomsgs, tableresults, data_purity Service Broker Msg 9708, State 1: The messages in the queue with ID 508580900 are referencing the invalid conversation group 'C4019AFF-E07D-E111-B666-0019BBE6ABC6'.CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.I've ended up all the conversations related to the queue 508580900. But the messages still there. So a question comes, Is it possible to fix this consistency errorsby deleting potencial problematic records (in this case) from the Service broker set of tables ? So, after some research I see just 1 possibility.1- Restore the database from the healthy backupDont' see as option to run again DBCC CHECKDB WITH ALLOW_DATA_LOSSDo you think it could be a different workaround without restoring the db ?Thanks team. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-03-22 : 15:54:22
|
| I have run into the same issues on one of my systems - the only way we have been able to fix these is to run a DBCC CHECKDB WITH repair_allow_data_loss.I am still trying to figure out how this occurs, so we can prevent it from happening in the first place - but no luck so far. I suspect it is due to poison messages that are not getting removed correctly and the conversations ended. No proof of that though... |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
psharpebr
Starting Member
2 Posts |
Posted - 2013-03-22 : 16:47:05
|
| Hi Jeff, Based on your experience with that, what was the real impact on running DBCC CHECKDB WITH repair_allow_data_loss?Did you suffer any lost that could compromise the other service broker tables ? What type of test you did to make sure no critical info was compromised ?thanks. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-03-25 : 13:43:39
|
| On our systems - the service brokers are not a critical part of the application and can be rebuilt. So, after running the repair_allow_data_loss we did not see any issues.Note: this issue has only occurred in our test environment where we are testing the upgrade process. How this will actually affect our production environment is unknown at this time. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-03-28 : 14:06:38
|
| Update: I was able to figure out how to remove those stranded messages without having to perform a repair. All it took was to force all conversations to end with cleanup (END CONVERSATION ... WITH CLEANUP). This leaves the messages in the stranded state...Next, just start receiving the messages from the queue (RECEIVE * FROM {queue}) until all stranded messages have been received.Validate you no longer have any stranded messages or conversation groups:SELECT * FROM sys.conversation_groups;SELECT * FROM {send queue};SELECT * FROM {receive queue};If the above queries no longer show the stranded messages - you should no longer have a problem.Note: be aware that is this is done on an active production system it could cause issues. Make sure you put the database in single user mode to insure no additional messages could be processing through your queue before ending the conversations and receiving the stranded messages. |
|
|
|
|
|
|
|
|
DBCC Consistency Error Service Broker