DB Consistency errors

  • Hi,

    Couple of my production databases reported Consistency error

    (more than 800 errors per databases).

    CHECKALLOC reported 0 errors.

    I repaired them with allow_data_loss as this was the minimum DBCC repair level.

    My DBs are fine now but I want to check when exactly those consistency errors started coming in and then to find out the root cause.

    I am exploring everything so far and so how looking out for similar expertise from you guys. Thanks

    VS

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • can you post some sample error message

    Rajesh Kasturi

  • Hi Vishal,

    I am surprised that without root cause analysis you used dbcc with allow data loss and that too in production. Can you post the error messages which you got before applying dbcc with allow data loss.

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • Chandu (7/27/2009)


    Hi Vishal,

    I am surprised that without root cause analysis you used dbcc with allow data loss and that too in production. Can you post the error messages which you got before applying dbcc with allow data loss.

    Because conistency was since two weeks and we had no times to do an RCA first and then repair the DB so I am doing RCA now.

    the Smaple errors were like this:

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

    Table error: Object ID 206623779. The text, ntext, or image node at page (1:4846), slot 7, text ID 18859360256 is not referenced.

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

    Table error: Object ID 206623779. The text, ntext, or image node at page (1:4846), slot 9, text ID 18859425792 is not referenced.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • You can get consistency error mostly for

    the values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005 and later, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error. (Source - Microsoft).

    So please check in your procedure wherver you are importing data into database.

    Manoj

  • You can get consistency error mostly for

    the values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005 and later, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error. (Source - Microsoft).

    So please check in your procedure wherver you are importing data into database.

    Manoj

  • You can get consistency error mostly for

    the values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005 and later, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error. (Source - Microsoft).

    So please check in your procedure wherver you are importing data into database.

    Manoj

  • Hi,

    Please post complete erorr messages. As this will help to find out any other issues also.

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • Hi,

    Please post complete erorr messages. As this will help to find out any other issues also.

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • Hi,

    Please post complete erorr messages. As this will help to find out any other issues also.

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • Vishal

    First thing - Do you have proper backups?

    check this article http://www.sqlservercentral.com/articles/65804/

    "Keep Trying"

  • The root cause will be IO subsystem problems. Corruption is (99% of the time) the result of problems somewhere in the IO path. Check anti-virus, check drivers, check HBAs, check SAN switches, check the SAN fabric, etc....

    Why on earth did you decide to repair? If the errors were recent and your backups good, you should have been able to restore fully with no data loss, and probably a fair bit faster.

    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
  • You can get consistency error mostly for

    the values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005 and later, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error. (Source - Microsoft).

    My databases are on 2000 only where I believe there is no DATA_PURITY option available?

    Also, since couple of months there is no changes in any of my procs and this consistency errors start coming in just since few days.

    wouldn't it put the possibility of inserting invalid data to question?

    Apart of any disk level problem (which I am checking now) what else could be the possibility?

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • GilaMonster (7/28/2009)


    The root cause will be IO subsystem problems. Corruption is (99% of the time) the result of problems somewhere in the IO path. Check anti-virus, check drivers, check HBAs, check SAN switches, check the SAN fabric, etc....

    Why on earth did you decide to repair? If the errors were recent and your backups good, you should have been able to restore fully with no data loss, and probably a fair bit faster.

    For restoring from backups I had to go 15 days back because I have a full backup running everyday (WITN INIT) and the conistency error I found was started 10-14 days back.

    pretty much through...isn't?

    restoring may caused loosing others data (pertty much more than what I lost while repairing with data_loss).

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • Additionally, Look on to the error message Its Error 8964:

    Was there any other alternative in my situation?

    as there any other alternative in my situation.?

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

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

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