August 29, 2007 at 9:26 am
Hi,
I having serious database corruption issue. I tried dbcc checkdb ('dbname', [REPAIR_REBUILD]), but still no repair. I would really appreciate if anybody could help me to recover my data.
DBCC CHECKDB gives the following error messages:
DBCC results for 'KCHKHR'.
DBCC results for 'sysobjects'.
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects' (ID 1) (index ID 2). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:2641:81) with values (name = 'PK__GLJL0048__10C1B270' and uid = 6 and id = 281129584) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects' (ID 1) (index ID 2). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:2641:82) with values (name = 'PK__GLJL0049__12A9FAE2' and uid = 6 and id = 313129698) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects' (ID 1) (index ID 2). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:2641:84) with values (name = 'PK__GLJL0050__14924354' and uid = 6 and id = 345129812) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:406:67) with values (parent_obj = 0 and id = 297129641) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:406:74) with values (parent_obj = 0 and id = 329129755) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:479:133) with values (parent_obj = 265129527 and id = 281129584) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:479:135) with values (parent_obj = 297129641 and id = 313129698) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'KCHKHR', index 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:479:148) with values (parent_obj = 329129755 and id = 345129812) points to the data row identified by ().
There are 898 rows in 18 pages for object 'sysobjects'.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'sysobjects' (object ID 1).
DBCC results for 'sysindexes'.
Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 64671055872 owned by data record identified by RID = (1:2309:16) id = 521130439 and indid = 16.
Msg 8929, Level 16, State 1, Line 1
August 30, 2007 at 12:37 am
If you get any errors from a DBCC then BOL says to restore from a backup (see DBCC CHECKDB (Transact-SQL) ).
If this is not possible could you try to copy the data out of your database then load it into a new database?
August 30, 2007 at 6:58 am
It appears to me form DBCC log that you need to Rebuild your indexes. It might be worth trying.
First take a backup of your current database.
Drop all your current indexes
Rebuild your indexes.
It may solve the issue.
Best of luck.
August 30, 2007 at 10:02 am
I don't think that the indexes are the issue here, but the system table sysindexes that is of issue. However, dropping and adding the indexes that it references "may" clean it up.
August 31, 2007 at 5:39 am
Before I resort to restoring a backup, I would first attempt
DBCC CHECKDB
with the assorted repair options (REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS). Only if these failed would I restore from backup.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
August 31, 2007 at 6:16 am
Mike.... It looks like the OP tried that. Is "allow data loss an options?"
August 31, 2007 at 8:14 am
Its truly a problem with the indexes. Just rebuild the indexes and your problems will be solved.
September 12, 2007 at 6:58 pm
That's terrible advice. You're advocating always choosing to lose data rather than restore from a backup. Why take backups if you're happy running repair?
Unless you have serious downtime constraints and your backup strategy doesn't allow a fine-grained restore, you should ALWAYS rely on your backups before running a repair that will lose data.
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 12, 2007 at 7:00 pm
This is bad advice - if the non-clustered indexes are enforcing constraints, dropping them allows operations that could break the constraint and then you wouldn't be able to recreate the indexes. Rebuilding them is good enough - but in this case won't help because its a system table.
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 12, 2007 at 7:04 pm
The only way for you to fix this is to restore from a backup as this type of system table corruption won't be fixed by CHECKDB in SQL 2000 (which I think you're running). If you don't have a backup, and the corruptions are limited to system table non-clustered indexes, you should be able to export all the user table data into a new database.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply