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
 Unable to repair Consistency errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jawad Khan
Starting Member

21 Posts

Posted - 07/15/2014 :  11:18:55  Show Profile  Send Jawad Khan a Yahoo! Message  Reply with Quote
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,

Edited by - Jawad Khan on 07/17/2014 05:31:35

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/15/2014 :  12:40:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
Restore from backup.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Jawad Khan
Starting Member

21 Posts

Posted - 07/15/2014 :  22:35:28  Show Profile  Send Jawad Khan a Yahoo! Message  Reply with Quote
I would have restored from backup if I had one... but alas! I've no backup.... :(

Please help!

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/15/2014 :  22:47:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Jawad Khan
Starting Member

21 Posts

Posted - 07/16/2014 :  05:27:02  Show Profile  Send Jawad Khan a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

Shanky
Starting Member

United Kingdom
48 Posts

Posted - 07/16/2014 :  07:07:12  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/16/2014 :  12:27:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Jawad Khan
Starting Member

21 Posts

Posted - 07/17/2014 :  05:31:10  Show Profile  Send Jawad Khan a Yahoo! Message  Reply with Quote
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....
Go to Top of Page

Shanky
Starting Member

United Kingdom
48 Posts

Posted - 07/17/2014 :  06:17:56  Show Profile  Reply with Quote
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
Go to Top of Page

Lincolnburrows
Starting Member

31 Posts

Posted - 08/04/2014 :  07:03:02  Show Profile  Reply with Quote
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.

Edited by - Lincolnburrows on 08/04/2014 07:03:40
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.11 seconds. Powered By: Snitz Forums 2000