errors found by DBCC CHECKDB

  • Dear friends ,

    I was wondering about this situation.

    After some search i have find out that many people has this problem.

    worst one has a Db about 6 Tera Byte and...

    Do you know why this kind of errors happening?

    I mean Why SQLServer makes pages with errors like this?

    For example :

    Page (1:1000698) in database ID 65 is allocated in the SGAM (1:511233) and PFS (1:994824), but was not allocated in any IAM.

    Best Regards,
    Ashkan

  • SQL Server doesn't. Corruption is, in the vast majority of cases due to a faulty IO subsystem. That means that there's a problem somewhere in your drives, san controller, fibre switch, firmware, cables, etc.

    If you want help fixing, run the following and post the fill and unedited results

    DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    Take a look at this article. http://www.sqlservercentral.com/articles/65804/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dear Gail,

    Thanks for reply,

    I have used that command and it returned about 3000 line of error code:

    This is last lines.

    Server: Msg 8948, Level 16, State 1, Line 1

    Database error: Page (1:7335) is marked with the wrong type in PFS page (1:1). PFS status 0x70 expected 0x60.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:7335) with latch type SH. VerifyPageId failed.

    CHECKDB found 1563 allocation errors and 0 consistency errors not associated with any single object.

    CHECKDB found 1563 allocation errors and 0 consistency errors in database 'FAQ'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FAQ ).

    Then I used this commands:

    ALTER DATABASE FAQ

    SET single_user WITH ROLLBACK IMMEDIATE;

    go

    DBCC checkdb ('FAQ', repair_allow_data_loss);

    go

    it returned 500 line of report (some errors and some repaired text) with this line at the end;

    CHECKDB found 1000 allocation errors and 0 consistency errors in database 'FAQ'.

    CHECKDB fixed 500 allocation errors and 0 consistency errors in database 'FAQ'.

    repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (FAQ repair_allow_data_loss).

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    then i used :

    ALTER DATABASE FAQ

    SET single_user WITH ROLLBACK IMMEDIATE;

    go

    DBCC checkdb ('FAQ',repair_fast );

    go

    and it returned 500 line of report (some errors and some repaired text) with this line at the end(like before);

    CHECKDB found 1000 allocation errors and 0 consistency errors in database 'FAQ'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FAQ repair_fast).

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    and the loop is continued.

    what should i do now??!!?!?:(

    Best Regards,
    Ashkan

  • I think i should post a new topic for this error:(

    Best Regards,
    Ashkan

  • ashkan siroos (10/16/2010)


    Dear Gail,

    Thanks for reply,

    I have used that command and it returned about 3000 line of error code:

    I need to see them ALL to give you any useful advice. Save the list in a text file, zip and attach to your post.

    Then I used this commands:

    ALTER DATABASE FAQ

    SET single_user WITH ROLLBACK IMMEDIATE;

    go

    DBCC checkdb ('FAQ', repair_allow_data_loss);

    go

    it returned 500 line of report (some errors and some repaired text) with this line at the end;

    *sigh* Repair is never the first resort for fixing corruption. Done is done I suppose, do you have any idea how much data the repair lost?

    and the loop is continued.

    what should i do now??!!?!?:(

    Without seeing all the errors, I can't give you a definitive answer, but likely either you have an IO subsystem that's producing corruption faster than you can fix it, or you have something that's irreparable.

    Do you have a clean backup of this database?

    I think i should post a new topic for this error

    I think you should not. No point having multiple posts for the same problem, one will just get locked.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ok , i don't know how to upload text file here so i will paste it here:

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6836) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6836) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6837) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6837) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6838) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6838) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6839) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6839) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6840) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6840) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6841) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6841) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6842) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6842) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6843) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6843) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6844) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6844) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6845) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6845) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6846) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6846) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6847) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6847) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6848) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6848) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6849) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6849) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6850) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6850) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6851) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6851) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6852) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6852) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6853) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6853) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6854) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:6854) with latch type SH. VerifyPageId failed.

    Server: Msg 8906, Level 16, State 1, Line 1

    Page (1:6855) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    Best Regards,
    Ashkan

  • 🙁 I cant paste it all:(

    how can i upload a file here?

    Best Regards,
    Ashkan

  • When composing your post, look below, in the "Post Options" section. There is a section titled "Attachments", with a button on the right that says "Edit Attachments". Click that button. In the popup, click the "Browse" button, select the file to upload. Click the "Upload Attachments" button. Click the "Close Window" link.

    Then, click on the "Post Reply" button.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Zip your text file and then attach it.

    Also, what's the output of the following?

    SELECT @@version

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have attached the file and also the output of @@version is:

    Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    Best Regards,
    Ashkan

  • I still need you to answer one question of mine:

    GilaMonster (10/16/2010)


    Do you have a clean backup of this database?

    That means a backup that does not have this corruption.

    p.s. Was that list that you posted from after the repairs ran or before?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 🙁 no I don't have a clean back up.

    And that report is the last report.

    Best Regards,
    Ashkan

  • help please:(

    Best Regards,
    Ashkan

  • I had a chat with an expert on this. It's not good news.

    If checkDB with repair allow data loss is not repairing these errors, it means that for some reason they are not repairable. Since you have no good backup, the only way that this can be fixed is if you script all the objects, export all the data that you can and recreate this as a new database.

    You also might want to look into your integrity check and backup jobs as they are clearly not adequate.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I'd also suggest that you do a detailed examination of your IO subsystem, check firmware versions, check and and all logs, look for anything out of place.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 15 total)

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