Torn Page - HEEELPPPPP - SQL Server Forums

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

Torn Page - HEEELPPPPP

Author

Topic

bpeski
Starting Member

4 Posts

Posted - 2009-05-13 : 22:08:16

Hi - I know there are other posts regarding torn pages. BUt I am hoping someone can provide a ray of hope here. I have a SQL server 2000 with a 500 MB database that went corrupt last Wednesday at 7:00 pm - we did not realize until Monday at 5:00 pm when data started to go missing. I tak hourly backups but to go back to Last Wednesday would be horrible interms of lost data. I have done lots of research and am hoping I can "save" some data. The results of my DBCC CheckDB are:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:29233) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2, index ID 255, page (1:29233). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1035150733, index ID 0, page ID (1:47326). The PageId in the page header = (1:47324).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1035150733, index ID 0: Page (1:47326) could not be processed. See other errors for details.
DBCC results for 'SMART_RESTORE'.
There are 0 rows in 0 pages for object 'sysindexes'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2).
There are 0 rows in 0 pages for object 'tblNotes'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'tblNotes' (object ID 1035150733).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'SMART_RESTORE'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SMART_RESTORE ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I can not "decify" this cryptic result. Is there any hopes of recovering data (I have read about rebulding indexes, but am not sure how).

Thanks in advance for your help!

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-05-14 : 10:04:44

Looks like you've got some kind of stale read problem going on.

If you don't want to go back in time, you've got two options:
1) run repair, which will lose one page of data from tblNotes, and lose some index statistics
2) extract the data out into another database, losing the same amount of data

If I were you (and your mileage may vary), I'd run repair after moving the database to a new IO subsystem.

Then you need to find and fix the problem in the IO subsystem.

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

bpeski
Starting Member

4 Posts

Posted - 2009-05-14 : 12:53:26

Hey Paul,
Thanks for the reply. Are you suggesting moving the data and log fiels to a another location, then running the dbbc Checkdb command (trying to figure our what you meanby repair). If you can provide the syntax that would be GREAT!!!

Thanks agian

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-05-14 : 15:06:00

Depends if you have somewhere else to move to. Repair is an option for CHECKDB - syntax and caveats in Books Online for DBCC CHECKDB.

Take a backup of the corrupt database before running repair, just in case something goes wrong, you can always go back again.

Thanks

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

bpeski
Starting Member

4 Posts

Posted - 2009-05-14 : 15:38:23

Hi paul,

THanks for the reply. Ok so I ran the repair (I restored a backup to a completetly different machine and ran the repir on that machine) and htis is what I got - I am assuming I am in deep.

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 448078479360 owned by data record identified by RID = (1:29391:3) id = 1163867213 and indid = 23.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:29233) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2, index ID 255, page (1:29233). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:29233), slot 0, text ID 448078479360 is referenced by page (1:3561), slot 6, but was not seen in the scan.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1035150733, index ID 0, page ID (1:47326). The PageId in the page header = (1:47324).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1035150733, index ID 0: Page (1:47326) could not be processed. See other errors for details.
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.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'sysindexes' (object ID 2).
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.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1035150733, index ID 1. Page (1:47326) was not seen in the scan although its parent (1:62889) and previous (1:48161) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1035150733, index ID 1. Page (1:47902) is missing a reference from previous page (1:47326). Possible chain linkage problem.
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 4 consistency errors in table 'tblNotes' (object ID 1035150733).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'SMART_REST'.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-05-14 : 19:46:15

Did you use REPAIR_ALLOW_DATA_LOSS?

It won't repair the sysindexes errors - you'll need to drop/recreate statistics.

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

bpeski
Starting Member

4 Posts

Posted - 2009-05-15 : 07:49:15

Hi Paul,

Here is the command I ran
ALTER DATABASE SMART_REST
SET single_user WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (SMART_REST, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
Go

And this is the result - again I ran this on another machine.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 448078479360 owned by data record identified by RID = (1:29391:3) id = 1163867213 and indid = 23.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:29233) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2, index ID 255, page (1:29233). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:29233), slot 0, text ID 448078479360 is referenced by page (1:3561), slot 6, but was not seen in the scan.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1035150733, index ID 0, page ID (1:47326). The PageId in the page header = (1:47324).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1035150733, index ID 0: Page (1:47326) could not be processed. See other errors for details.
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.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'sysindexes' (object ID 2).
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.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1035150733, index ID 1. Page (1:47326) was not seen in the scan although its parent (1:62889) and previous (1:48161) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1035150733, index ID 1. Page (1:47902) is missing a reference from previous page (1:47326). Possible chain linkage problem.
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 4 consistency errors in table 'tblNotes' (object ID 1035150733).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'SMART_REST'.

Thanks again Paul for all your time and help!!!

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-15 : 09:46:56

quote:
Originally posted by paulrandal

It won't repair the sysindexes errors - you'll need to drop/recreate statistics.

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

Did you do the additional step Paul suggested? Either rebuild your indexes or recreate statistics and then run DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS. Post back the results if you still have issues.

Terry

-- Procrastinate now!

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-05-15 : 11:34:42

Concerned that the tblNotes errors aren't being fixed - no reason those errors shouldn't be repaired, but maybe the system table ones are tripping up the code (don't remember all the internals of the 2000 CHECKDB).

Can you try doing a DBCC CHECKTABLE ('tblNotes', REPAIR_ALLOW_DATA_LOSS) to fix the user table errors?

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources