DBCC CHECKTABLE not reporting errors

Question

DBCC CHECKTABLE not reporting errors

EarnestGoesWest

SSC Enthusiast

Points: 120
More actions
September 27, 2013 at 3:50 am

#283152

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

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

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

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 1

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

EarnestGoesWest SSC Enthusiast Points: 120 More actions · Answer 2

Hi,

Here it is.

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

Thanks.

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

EarnestGoesWest SSC Enthusiast Points: 120 More actions · Answer 5

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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