DBCC CHECKTABLE not reporting errors

  • Hi,

    I've recently implemented a new DBCC INTEGRITY check process running a combination of CHECKDB (for the smaller databases) and CHECKTABLE for larger - spread over a few days.

    During my testing of the CHECKTABLE element, I've noticed that the CHECKTABLE does not always report the integrity errors. :w00t:

    If I repeatably run the check, about 1 in 4 times it reports no errors at all, the other times it does - see below :-

    ERRORS :-

    ErrorLevelStateMessageText

    8928161Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data): Page (1:79) could not be processed. See other errors for details.

    89391698Table error: Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data), page (1:79). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.

    2593101There are 911 rows in 11 pages for object "CorruptTable".

    8990101CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'CorruptTable' (object ID 2105058535).

    No ERRORS :-

    ErrorLevelStateMessageText

    2593101There are 911 rows in 11 pages for object "CorruptTable".

    I should add, for the test, I manually corrupted the "test" database by manually editing the MDF files on a particular value. This issue is occuring on SQL2008(SP2), on my SQL2012 instance it seems to behave itself as expected. I appreciate that this could because this is a forced corruption, but surely a corrupt database is still a corrupt database by whatever means.

    Has anyone else seen this behavour?

    Thanks

  • EarnestGoesWest (9/27/2013)


    Hi,

    I've recently implemented a new DBCC INTEGRITY check process running a combination of CHECKDB (for the smaller databases) and CHECKTABLE for larger - spread over a few days.

    During my testing of the CHECKTABLE element, I've noticed that the CHECKTABLE does not always report the integrity errors. :w00t:

    If I repeatably run the check, about 1 in 4 times it reports no errors at all, the other times it does - see below :-

    ERRORS :-

    ErrorLevelStateMessageText

    8928161Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data): Page (1:79) could not be processed. See other errors for details.

    89391698Table error: Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data), page (1:79). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.

    2593101There are 911 rows in 11 pages for object "CorruptTable".

    8990101CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'CorruptTable' (object ID 2105058535).

    No ERRORS :-

    ErrorLevelStateMessageText

    2593101There are 911 rows in 11 pages for object "CorruptTable".

    I should add, for the test, I manually corrupted the "test" database by manually editing the MDF files on a particular value. This issue is occuring on SQL2008(SP2), on my SQL2012 instance it seems to behave itself as expected. I appreciate that this could because this is a forced corruption, but surely a corrupt database is still a corrupt database by whatever means.

    Has anyone else seen this behavour?

    Thanks

    show us the code?..thanks

  • Hi,

    Here it is.

    DBCC CHECKTABLE ('[dbo].[CorruptTable]') WITH ALL_ERRORMSGS, TABLERESULTS;

    Thanks.

  • EarnestGoesWest (9/27/2013)


    Hi,

    Here it is.

    DBCC CHECKTABLE ('[dbo].[CorruptTable]') WITH ALL_ERRORMSGS, TABLERESULTS;

    Thanks.

    i hope this will help

    http://www.sqlsoldier.com/wp/sqlserver/day24of31daysofdisasterrecoveryhandlingcorruptioninaclusteredindex

  • If there is corruption, CheckDB will always find it. If you've manually edited an unallocated page, or the middle of a page when you don't have page checksums enabled, then the changes may go unnoticed. Unallocated pages aren't checked because there's no way to tell whether there's damage or not, they're unallocated so they're allowed to be absolute garbage.

    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
  • Thanks for your response.

    The page I modified was allocated as it was a page containing the orginal data inserted into the table.

    DBCC CHECKDB does fail and correctly report the error, also MSDB contains the page within SUSPECT_PAGES. DBCC CHECKTABLE is not reporting the error consistantly is my primary concern, especially as I am planning to make use of CHECKTABLE on our production databases.

    Thanks.

  • You can't just run checktable on all tables, you need check alloc and check catalog too. Could be that it's one of those phases that's failing checkDB. Can't tell without more info.

    CheckDB is Check Alloc, Check Catalog and then Check table on all tables.

    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 7 posts - 1 through 6 (of 6 total)

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