Unable to repair Consistency errors

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

Unable to repair Consistency errors

Author

Topic

Jawad Khan
Starting Member

21 Posts

Posted - 2014-07-15 : 11:18:55

I searched a lot but could not find any solution.
When I run this command,

DBCC CHECKDB() WITH NO_INFOMSGS, ALL_ERRORMSGS;

Output>>
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:282). The PageId in the page header = (0:0).
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 7359758336 owned by data record identified by RID = (1:97:0) id = 326292222 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 9260302336 owned by data record identified by RID = (1:97:9) id = 326292222 and indid = 10.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:282) could not be processed. See other errors for details.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:282), slot 0, text ID 7359758336 is referenced by page (1:217), slot 8, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:282), slot 1, text ID 9260302336 is referenced by page (1:285), slot 1, but was not seen in the scan.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 6 consistency errors in database '_RIZWAN'.

---------------
When I run this command using db 'master',

ALTER DATABASE _rizwan SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB ('_rizwan', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
GO

Output>>
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:282). The PageId in the page header = (0:0).
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 7359758336 owned by data record identified by RID = (1:97:0) id = 326292222 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 9260302336 owned by data record identified by RID = (1:97:9) id = 326292222 and indid = 10.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:282) could not be processed. See other errors for details.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:282), slot 0, text ID 7359758336 is referenced by page (1:217), slot 8, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:282), slot 1, text ID 9260302336 is referenced by page (1:285), slot 1, but was not seen in the scan.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
The repair level on the DBCC statement caused this repair to be bypassed.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 6 consistency errors in database '_RIZWAN'.

-------
I tried to run this query;

select OBJECT_NAME(id), name, CASE INDEXPROPERTY(id, name, 'IsStatistics') WHEN 1 THEN 'Statistics' WHEN 0 THEN 'Index' END as Type
from sysindexes
WHERE id = 326292222 AND indid in (1,10)

It came with an index and a statistics as result, but I am not able to drop any;

Also dropping PK_ACCOUNTS in Enterprise manager it says:

'ACCOUNTS' table
- Unable to delete index 'PK_ACCOUNTS'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (bad page ID) detected during read at offset 0x00000000234000 in file '[path]MillsDBV1_Data.mdf'.

-------------

--EDIT-- I forgot to mention that I am using SQL Server 2000.

Please help, i am really in hot waters :P

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-15 : 12:40:36

Restore from backup.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Jawad Khan
Starting Member

21 Posts

Posted - 2014-07-15 : 22:35:28

I would have restored from backup if I had one... but alas! I've no backup.... :(

Please help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-15 : 22:47:53

SQL 2000 is over a decade old. You must have some backup that can be restored to at least get some data back and some of the schema.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Jawad Khan
Starting Member

21 Posts

Posted - 2014-07-16 : 05:27:02

Thanks Tara Kizer for your replies...

So you mean to say that there is no way the consistency errors can be repaired!?

I've a copy of database in separate folder but that is empty (no data).... No backups have been taken after when data is inserted to database.

There should be a way to repair those errors with some data loss.

Please someone help...

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-07-16 : 07:07:12

This should go as a message why backup is SO MUCH important.

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-16 : 12:27:08

quote:
Originally posted by Jawad Khan

There should be a way to repair those errors with some data loss.

You could try purchasing a 3rd party tool that can recover data from a corrupt database. There are many out there. For SQL 2000, you might even find one that is free.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Jawad Khan
Starting Member

21 Posts

Posted - 2014-07-17 : 05:31:10

I managed to export the data from the damaged database to the blank one although last 30 or so rows from one table were gone.. but still it is good enough for me.

Thank you guys for your responses.. and yes.. the backup is must.. lesson learned :)

Not sure how to close the thread.. but this issue is solved now....

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-07-17 : 06:17:56

Jawad thats the best thing to do, I am happy you extracted as much data as possible and now create a proper backup strategy

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-08-04 : 07:03:02

There are 2 other commands by which you completely eradicate SQL database consistency error and they don't destroy anything.
REPAIR_FAST:
Performs minor, non time-consuming repair actions such as repairing extra keys in non clustered indexes. These repairs can be done quickly and without risk of data loss.
&
REPAIR_REBUILD:
Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources