Post reply

Corrupt table ! What can be done ?

  • 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)

  • September 15, 2006 at 4:21 am

    #660642

    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

  • September 15, 2006 at 5:17 am

    #660651

    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)

  • September 16, 2006 at 10:31 am

    #660887

    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

  • September 16, 2006 at 8:14 pm

    #660905

    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

  • September 17, 2006 at 12:23 am

    #660909

    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

  • September 17, 2006 at 7:05 am

    #660913

    Mubeen

    <If it has Bas Sectors>

    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

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

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