Corrupt table ! What can be done ?

Question

Corrupt table ! What can be done ?

John Burchel

Hall of Fame

Points: 3611
More actions
September 15, 2006 at 4:06 am

#60860

Hi All,

I have a corrupt table on the database, anytime a user tries to run a query on it, it returns on error.

If you run select top 1000 * from the table, it works.
But if you include a where clause, then it turns up with an error.

I/O error (bad page ID) detected during read at offset 0x0000008903a000 in file 'Database_File_Name.

I tried to BCP the data out, normally the data should be around 3GB in size in txt format, its only reaching 250mb and it fails on BCP.

I tried selecting into another table, I get a torn page error.

DBCC CheckDB Error

CHECKDB found 0 allocation errors and 10 consistency errors in table 'PersonProfile’ (object ID 689658156).

Is there anything that I can do to sort this out ?

Kindest Regards,
John Burchel (Trainee Developer)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John

The best way to recover from a torn page is to restore the database from a good backup. If you don't have that option (and if you don't then you need to revise your backup and restore strategy) then look at the various options you can use with DBCC CHECKDB. You'll find it in Books Online. Also, you should enable torn page detection on your database so that the next time this happens, you'll know immediately.

Incidentally, the best way to avoid torn pages in the first place is to have a write cache on your disk that is backed up by a battery. That way, if power is unexpectedly lost, the whole of the data page that was being written to disk can be completed. Ask your storage administrator or vendor about this.

John

John Burchel Hall of Fame Points: 3611 More actions · Answer 2

I done a DBCC Checktable and it returned 10 consistency errors.

Is there no way i can get all the data on this table out of the system safely

Kindest Regards,

John Burchel (Trainee Developer)

Mubeen Mohammed-203067 SSC Eights! Points: 877 More actions · Answer 3

John,

Use this from QA

SELECT COUNT(*) FROM <CORRUPTED_TABLE_NAME>

Note down the Total No. of Records from a table.

SELECT * FROM <CORRUPTED_TABLE_NAME>

check how many records it is selecting (if you have bad data in the table, then at some point it will stop and produce error) just check the last KEY COLUMN record which it selected from select * from table. Next step is

SELECT * FROM <CORRUPTED_TABLE> WHERE KEY_COLUMN = VALUE

In the above query the column VALUE should be something like, say your table has a key field as EMPNO and the last empno it returned as 15547 then you have to check with next record as 15548 as the next value.

If it doesn't return any record(s) it means from then onwards some record(s) are corrupted.

Now take the same query and execute in Enterprise Manager, First Right Click on Table from EM and select Open Table and in SQL Editor in EM use the query and it returns the bad value record from EM. There is difference in EM and QA in selecting Records, I hope you will get thru this.

Thanks,

Mubeen

EugeneZ-162636 Default port Points: 1448 More actions · Answer 4

sounds like bad sectors on your drive

Chkdsk:

http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/prork/pref_tts_ffgh.mspx?mfr=true

---

also run

DBCC DBREINDEX

http://msdn2.microsoft.com/en-us/library/ms181671.aspx

Mubeen Mohammed-203067 SSC Eights! Points: 877 More actions · Answer 5

If it has Bad Sectors, DBREINDEX also fails. Index Defragmentation will success but will not fix the Bad data. This seems to be some records corrupted say for ex: like float data types, divide by zero error or data corrupted bcoz of various equations and with various reasons.

Thanks,

Mubeen

EugeneZ-162636 Default port Points: 1448 More actions · Answer 6

Mubeen

there is error< I/O error (bad page ID)>

and normaly - if no CheckDB and DBreindex helps

you will run chkdsk

then as in my post you will run DBCC DBreindex