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.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 DBCC Consistency Error Service Broker

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 command

DBCC 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 errors
by 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 backup

Dont' see as option to run again DBCC CHECKDB WITH ALLOW_DATA_LOSS

Do 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...
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-22 : 16:18:30
Possible cause (and help?): http://rusanu.com/2010/03/09/dealing-with-large-queues/

Lots of good SB troubleshooting tips on his blog.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -