September 17, 2009 at 11:31 am
Hi
Here is one of the Production DB's which has 16 consistency errors when I run DBCC Checkdb. Can anybody suggest me what to do ? Will this error be resolved if I opt for DBCC Checkdb with REPAIR_REBUILD option ?
Error :
DBCC results for 'subcontent'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2014630220, index ID 0: Page (1:309989) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 2014630220, index ID 0, page ID (1:309990). The PageId in the page header = (1:309998).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2014630220, index ID 0: Page (1:309990) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 2014630220, index ID 0, page ID (1:309991). The PageId in the page header = (1:309999).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2014630220, index ID 0: Page (1:309991) could not be processed. See other errors for details.
There are 660233 rows in 15708 pages for object 'subcontent'.
CHECKDB found 0 allocation errors and 16 consistency errors in table 'subcontent' (object ID 2014630220).
CHECKDB found 0 allocation errors and 16 consistency errors in database 'docbase'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB ('docbase' ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
September 17, 2009 at 11:39 am
No - you need to use the REPAIR_ALLOW_DATA_LOSS option, as the output clearly states, and if you do this you're going to lose data from the subcontent table.
The corruption looks like the I/O subsystem has written a block of pages 8 pages earlier in the file - my guess would be this coincides with a RAID stripe size and it's written a whole stripe one stripe early -> RAID controller issue.
Your only way to recover from this without data loss is to restore from backups. If you don't have backups then you'll have to run repair, which will lose data. Whichever you do, make sure you fix the I/O subsystem too otherwise it will likely happen again.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
September 17, 2009 at 12:12 pm
Hi Paul
I have seen some of the articles written by you like Checkdb from every angle. It is very good article.
I also see that If the database goes into suspect mode then there will be a data loss. But in my case the database is still up and running. I think if there are any allocation errors, then there will be a loss of data but here there are only consistency errors. I believe this will get resolved if I run REPAIR_REBUILD.
Please correct me If I am wrong.
Thanks
September 17, 2009 at 12:31 pm
Yes, you're wrong. The output from CHECKDB tells you that you need to use REPAIR_ALLOW_DATA_LOSS.
CHECKDB found 0 allocation errors and 16 consistency errors in database 'docbase'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB ('docbase' ).
The repairs for these errors are to delete the pages - these are data pages, not index pages.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 6, 2009 at 2:50 pm
Do you backup your transaction logs? If so, you may be able to restore the damaged pages.
http://www.sql-server-pro.com/dbcc-checkdb.html
Hope this helps
October 6, 2009 at 10:07 pm
I would recommend take full backup of live database, create a dummy database restore live database into the dummy DB.
Take total count of rows in live DB
Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS on Dummy database
Take total count of rows in dummy DB
Compare the amount of data lost.
If data lost is very negligible and your management can afford the data loss, you are good to go.
Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS on Live DB
If data loss id huge and management can’t afford it, then restore would be the final option.
Thanks
Samji
- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂
October 7, 2009 at 1:36 am
COOL_ICE (10/6/2009)
If data lost is very negligible and your management can afford the data loss, you are good to go.Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS on Live DB
If data loss id huge and management can’t afford it, then restore would be the final option.
Personally I'd say restore if there's a good backup, repair if there isn't. Especially if page-level restores can be done, that's really, really quick. Only time I might go for a repair over a restore (a repair that loses data) is when page-level restores aren't an option and minimal downtime is allowed, and even then, I'd probably push for restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply