Post reply

DBCC CHECKDB WITH TRY/CATCH

  • July 17, 2012 at 8:41 pm

    #276063

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

  • July 17, 2012 at 9:17 pm

    #1513979

    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]

  • July 18, 2012 at 2:18 am

    #1514058

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

  • July 18, 2012 at 2:54 am

    #1514078

    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

  • July 18, 2012 at 3:15 am

    #1514084

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

  • July 18, 2012 at 4:16 am

    This was removed by the editor as SPAM

  • July 18, 2012 at 5:45 am

    #1514145

    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

  • July 18, 2012 at 6:47 am

    #1514177

    Thank you Roberto for an interesting question.

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

  • July 18, 2012 at 7:35 am

    #1514209

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

  • July 18, 2012 at 7:53 am

    #1514221

    Interesting question. Thanks.

  • July 18, 2012 at 8:06 am

    #1514230

    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]

  • July 18, 2012 at 8:56 am

    #1514276

    good, straight forward question - cheers

  • July 18, 2012 at 9:28 am

    #1514291

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

  • July 18, 2012 at 10:00 am

    #1514323

    Nice question, learned something today.

  • July 18, 2012 at 10:11 am

    #1514331

    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 22 total)

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