SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 DBCC Consistency Error Service Broker
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

psharpebr
Starting Member

Costa Rica
2 Posts

Posted - 03/22/2013 :  15:13:57  Show Profile  Reply with Quote
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

USA
799 Posts

Posted - 03/22/2013 :  15:54:22  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 03/22/2013 :  16:18:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Costa Rica
2 Posts

Posted - 03/22/2013 :  16:47:05  Show Profile  Reply with Quote
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

USA
799 Posts

Posted - 03/25/2013 :  13:43:39  Show Profile  Reply with Quote
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

USA
799 Posts

Posted - 03/28/2013 :  14:06:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000