DBCC CHECKDB WITH TRY/CATCH

  • roberto.santarsiero

    Ten Centuries

    Points: 1284

    Comments posted to this topic are about the item DBCC CHECKDB WITH TRY/CATCH

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question ... is actual straight forward .. no gimmicks .. no gotchas ...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Toreador

    SSChampion

    Points: 11243

    Good question - but the "explanation" is just a repetition of the answer.

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Very interesting QODT.

    Here a little additional information that I find helpful:

    1. The most important explanation from the Support Link:

    When the DBCC CHECKDB statement detects a consistency error in the database, the DBCC CHECKDB statement records the error number and the error message of the consistency error. Additionally, the DBCC CHECKDB statement must detect all consistency errors inside the database. Therefore, the DBCC CHECKDB statement does not raise an exception when the DBCC CHECKDB statement detects the first consistency error so that the current execution is not interrupted.

    If you execute the SELECT @@ERROR statement immediately after the DBCC CHECKDB statement, the SELECT @@ERROR statement returns the last consistency error that the DBCC CHECKDB statement detects.

    The TRY…CATCH construct only intercepts exceptions that are raised from errors. Additionally, the CATCH block is triggered only if an exception is raised by an error that has a severity level between 10 and 20.

    2. In order to test corruption effects, the I found the following webpage very helpful:

    Corrupting Databases for Dummies- Hex Editor Edition

    by Kendra Little on January 24, 2011

    http://www.littlekendra.com/2011/01/24/corrupthexeditor/

    3. Interestingly, the error messages are nonetheless "swallowed" by the try-catch block (but the summary contains the correct error count).

    With try catch:

    DBCC results for 'DeadBirdies'.

    There are 500000 rows in 2295 pages for object "DeadBirdies".

    CHECKDB found 0 allocation errors and 4 consistency errors in table 'DeadBirdies' (object ID 2105058535).

    CHECKDB found 0 allocation errors and 4 consistency errors in database 'CorruptMe'.

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

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

    DBCC finished without any errors.

    Without try catch:

    DBCC results for 'DeadBirdies'.

    Msg 8928, Level 16, State 1, Line 1

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

    Msg 8939, Level 16, State 98, Line 1

    Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data), page (1:186). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

    Msg 8976, Level 16, State 1, Line 1

    Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data). Page (1:186) was not seen in the scan although its parent (1:2636) and previous (1:185) refer to it. Check any previous errors.

    Msg 8978, Level 16, State 1, Line 1

    Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data). Page (1:187) is missing a reference from previous page (1:186). Possible chain linkage problem.

    There are 500000 rows in 2295 pages for object "DeadBirdies".

    CHECKDB found 0 allocation errors and 4 consistency errors in table 'DeadBirdies' (object ID 2105058535).

    CHECKDB found 0 allocation errors and 4 consistency errors in database 'CorruptMe'.

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

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

    Best Regards,

    Chris Büttner

  • anthony.green

    SSC Guru

    Points: 112214

    Good question, prompted me to do some investigation on that as I have never wrapped DBCC CHECKDB within a TRY CATCH block before.

  • This was removed by the editor as SPAM

  • Thomas Abraham

    SSChampion

    Points: 10761

    Christian Buettner-167247 (7/18/2012)


    Very interesting QODT.

    Here a little additional information that I find helpful:

    Thanks Christian for some useful and interesting information. And thanks to Roberto for the question. Good stuff.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • sestell1

    SSChampion

    Points: 10230

    Thank you Roberto for an interesting question.

    Thanks Christian for the follow-up with some very useful details!

  • Ernie Schlangen

    SSCrazy

    Points: 2382

    Very interesting question. But MS should have made the answer easier to find, IMHO. Learned something = good day!:-D

  • KarenM

    SSChasing Mays

    Points: 608

    Interesting question. Thanks.

  • DugyC

    Hall of Fame

    Points: 3804

    When examined from a programmers background the answer came quite easily.

    DBCC CHECKDB is designed to look for consistency errors in the database, so why would finding them be an exception to its normal behaviour.

    Great question. thanks.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3944

    good, straight forward question - cheers

  • Andre Ranieri

    SSCrazy

    Points: 2759

    Thanks for the question! I usually start my day off with the SSS-QOTD to keep my knowledge fresh.

  • Ken Wymore

    SSCoach

    Points: 16432

    Nice question, learned something today.

  • EL Jerry

    SSCertifiable

    Points: 7053

    I've never used TRY...CATCH before, and did not read about "database has inconsistencies", so I got it wrong for testing on my databases with no inconsistencies. 😀

    Learned something new the hard way, though.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

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

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