Corrupt Tables/Index - 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

Corrupt Tables/Index

Author

Topic

Mighty Quinn
Starting Member

5 Posts

Posted - 2008-05-18 : 22:10:18

Hi All,

This database sits on SQL server 2000. The problem is with a copy of the production database. My concern is that if it is a hardware issue then I have more problems on my hand. Bellow are the errors. teh first one is from the SQL server log the second is using DBCC CHECKDB

Point to note : this a VMware server.

I/O error (torn page) detected during read at offset 0x0000000093a000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\PPFILESSQLT.mdf'..

*-*-*-*-

DBCC results for 'ppfilessqlt'.
DBCC results for 'sysobjects'.
There are 156 rows in 3 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 181 rows in 12 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 819 rows in 14 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 173 rows in 15 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 83 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 21 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 1350 rows in 69 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 508 rows in 3 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'PPFILES_L#MGP100'.
There are 9 rows in 1 pages for object 'PPFILES_L#MGP100'.
DBCC results for 'PPFILES_L#OHP100'.
There are 65966 rows in 1627 pages for object 'PPFILES_L#OHP100'.
DBCC results for 'MTDPromoSale'.
There are 0 rows in 0 pages for object 'MTDPromoSale'.
DBCC results for 'PPFILES_L#PGP100'.
There are 1028 rows in 21 pages for object 'PPFILES_L#PGP100'.
DBCC results for 'PPFILES_L#PRP100'.
There are 2772 rows in 65 pages for object 'PPFILES_L#PRP100'.
DBCC results for 'CalendarMaster'.
There are 6 rows in 1 pages for object 'CalendarMaster'.
DBCC results for 'PPFILES_L#RLP100'.
There are 73 rows in 2 pages for object 'PPFILES_L#RLP100'.
DBCC results for '~TMPCLP15451'.
There are 480 rows in 10 pages for object '~TMPCLP15451'.
DBCC results for 'PPFILES_L#POP100'.
There are 57 rows in 1 pages for object 'PPFILES_L#POP100'.
DBCC results for 'PPFILES_L#RUP100'.
There are 4 rows in 1 pages for object 'PPFILES_L#RUP100'.
DBCC results for 'PPFILES_L#SLP150'.
There are 0 rows in 1 pages for object 'PPFILES_L#SLP150'.
DBCC results for 'PPFILES_L#VIP100'.
There are 0 rows in 0 pages for object 'PPFILES_L#VIP100'.
DBCC results for 'Divisions'.
There are 10 rows in 1 pages for object 'Divisions'.
DBCC results for 'Brand'.
There are 121 rows in 2 pages for object 'Brand'.
DBCC results for 'UserList'.
There are 20 rows in 1 pages for object 'UserList'.
DBCC results for 'ItemProposal_H'.
There are 18 rows in 1 pages for object 'ItemProposal_H'.
DBCC results for 'MTDSale'.
There are 138 rows in 2 pages for object 'MTDSale'.
DBCC results for 'SystemSetup'.
There are 199 rows in 2 pages for object 'SystemSetup'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1726629194, index ID 0: Page (1:1181) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1726629194, index ID 0, page (1:1181). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'PPFILES_L#SYP100'.
There are 188 rows in 2 pages for object 'PPFILES_L#SYP100'.
DBCC results for 'PPFILES_L#CSP100'.
There are 11130 rows in 521 pages for object 'PPFILES_L#CSP100'.
DBCC results for 'Product'.
There are 10640 rows in 133 pages for object 'Product'.
DBCC results for 'ProductSetup'.
There are 15974 rows in 215 pages for object 'ProductSetup'.
DBCC results for 'PPFILES_L#ODP100'.
There are 1557837 rows in 16352 pages for object 'PPFILES_L#ODP100'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'PPFILES_L#ODP100' (object ID 1726629194).
DBCC results for 'ItemProposal_H_temp'.
There are 6 rows in 1 pages for object 'ItemProposal_H_temp'.
DBCC results for 'ItemProposal_D_Temp'.
There are 102 rows in 2 pages for object 'ItemProposal_D_Temp'.
DBCC results for 'Messages'.
There are 9 rows in 1 pages for object 'Messages'.
DBCC results for 'ItemProposal_D'.
There are 543 rows in 8 pages for object 'ItemProposal_D'.
DBCC results for 'dtproperties'.
There are 7 rows in 1 pages for object 'dtproperties'.
DBCC results for 'AnimationCode'.
There are 128 rows in 2 pages for object 'AnimationCode'.
DBCC results for 'L#RLP100_Back'.
There are 23235 rows in 664 pages for object 'L#RLP100_Back'.
DBCC results for 'Calendar'.
There are 81 rows in 1 pages for object 'Calendar'.
DBCC results for 'L#CSP100_Back'.
There are 486 rows in 35 pages for object 'L#CSP100_Back'.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'PPFILESSQLT'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PPFILESSQLT ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thanks
Neil

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-18 : 22:16:37

Sounds disk issue to me.

Mighty Quinn
Starting Member

5 Posts

Posted - 2008-05-19 : 00:52:39

I've also run the DBCC command with the REPAIR_FAST AND REPAIR_REBUILD options, but the errors remain.

I've run the DBCC option with the following options ALL_ERRORMSGS, NO_INFOMSGS and the result is shown below. there are a few extra error lines as compared to my first post

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1726629194, index ID 0: Page (1:1181) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1726629194, index ID 0, page (1:1181). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1726629194, index ID 1. Page (1:1181) was not seen in the scan although its parent (1:1018) and previous (1:1180) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1726629194, index ID 1. Page (1:1182) is missing a reference from previous page (1:1181). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'PPFILES_L#ODP100' (object ID 1726629194).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'PPFILESSQLT'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PPFILESSQLT ).

rmiao, thanks for your reply.

Paul is there anyway to confirm from teh error messages that it is indeed due to hardware problems?

Thanks.

data recovery
Starting Member

2 Posts

Posted - 2008-05-30 : 03:25:55

http://www.nucleustechnologies.com/sql-recovery.html

There is every possible solution to each problem like data recovery. Data recovery is possible in 85% of cases.
http://www.nucleustechnologies.com

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-05-30 : 08:40:57

Data-recovery - go away and only post when it's going to actually be useful. Yes, this might automate some of what you can do manually, but as with any other 'recovery' tool, it won't go anywhere if the database can't be repaired by CHECKDB in the first place. Use with caution.

Mighty Quinn - yes, this is a torn-page, which only happens when a drive loses power and can't complete a write operation. You'll need to restore from a backup otherwise you'll lose the data that was on page 1:1181. You could repair_allow_data_loss to fix things up but then you'll definitely lose the data. Or you could try running the advertized recovery tool - and then come back to running repair or restore :-)

Thanks

PS No - I don't like these hyped-up 'recovery' tools that just export as much data as they can.

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

Mighty Quinn
Starting Member

5 Posts

Posted - 2008-06-05 : 04:31:56

Hi Paul,

Thanks for yur answer.

I've done exactly that, I've restored teh databse from ab ackup and problem went away.

Thanks goodness it was only a test database

Regards
Neil

Martinha
Starting Member

3 Posts

Posted - 2016-05-09 : 10:15:10

Great, I'll try with this advice. I used the software of Nucleus and it does not work to repair a database. Finally i used AccessFIX.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources