CheckDB

  • Comments posted to this topic are about the item CheckDB

    [font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

  • The options you mentioned are not clear.

    It will fail when trying to run against tempdb database.

    It will fail when trying to run against the master database

    DBCC CheckDB does not "fail" while running against master or tempdb databases.

    "Keep Trying"

  • It is comprehensive and validates the logical and physical integrity of all the objects in the specified database including tables, indexes, indexed views & statistics.

    What's wrong with this statement?

    Best regards,
    Dietmar Weickert.

  • DBCC CHECKDB runs againt master and tempdb with no errors. I wouldn't say it fails, I would say it doesn't behave exactly as you expect.

    -- Gianluca Sartori

  • DBCC CHECKDB does NOT fail when run against tempdb.

  • Dietmar Weickert (8/17/2009)


    It is comprehensive and validates the logical and physical integrity of all the objects in the specified database including tables, indexes, indexed views & statistics.

    What's wrong with this statement?

    Ditto...

    I picked this option and it's the only reason I was wrong, be nice to know why that particular option is incorrect (I'm assuming it's the stats, but can't confirm that in BOL anywhere).

    For the few mentioning that DBCC CHECKDB does not fail on master or tempdb, you're right, but the way I read the question, it's not stating otherwise is it?

    --------
    [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

  • If only 2% of people are getting this correct, it's a sign of a badly written question.

    - Jeff

  • I picked this option and it's the only reason I was wrong, be nice to know why that particular option is incorrect (I'm assuming it's the stats, but can't confirm that in BOL anywhere).

    Same here - that is the only option I got wrong. I did find these links about stats after answering the QOD: Bug #: 443756, Bug 158623. They are both for 2K5, but probably still valid.

  • So what kind of an error message does the author recieve when running CHECKDB agains the tempdb? I am not seeing it.

  • I firmly believe the author of this question did NOT perform due diligence when selecting possible answers.

    The original statement

    Check all that apply that are true about

    In the case of the master and temp db the question statement should have been: Check all that may apply and are true about

    From Books On Line

    DBCC CHECKDB

    Runs DBCC CHECKALLOC on the database.

    Runs DBCC CHECKTABLE on every table and view in the database.

    Runs DBCC CHECKCATALOG on the database

    From DBCC CHECKALLOC.

    http://technet.microsoft.com/en-us/library/aa258809(SQL.80).aspx

    Note NOINDEX is maintained for backward compatibility only. All indexes are checked when executing DBCC CHECKALLOC.

    So in effect using DBCC CHECKDB which in turn invokes DBCC CHECLALLOC the correct answer is: does check all indexes

    On the last possible answer also see:

    CHECK STATISTICS

    http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=158623

    (Bold facing entered by myself to drive home my arguement)

    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]

  • I hesitated to answer because I was positive at least one of the answers was going to have some wacky interpretation.

    DBCC CHECKDB does not fail when run against either master or tempdb, unless some other conditions are met. It MIGHT fail against any database, given a sufficient level of data corruption or hardware failure, but it is not true that it WILL fail against tempdb or master.

    From BOL under "DBCC CHECKDB":

    Checks the logical and physical integrity of all the objects in the specified database

    I don't see any quibbles there about statistics. It may not refresh statistics, but it does check that they are correctly allocated.

  • Five people got the question correct so far, I am not sure how that happened.

  • First, I take the criticism that this could have been worded better. I wrote this probably 6-8 months ago but got published now so the context is NOT fresh off my mind.

    Check all that apply that are true about DBCC CHECKDB.

    1) It will fail when trying to run against tempdb database.

    FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspx

    Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.

    DBCC CHECKDB will run successfully against tempdb but may NOT perform all the checks as it does for other user databases. Note that it will NOT fail either. So, it is NOT true.

    2) It will fail when trying to run against the master database.

    FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspx

    when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database.

    DBCC CHECKDB will run successfully against master. So, it is NOT true.

    3) It uses tempdb space.

    This is obvious and is true.

    4) It examines all indexes in the database including the disabled indexes.

    FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspx

    DBCC CHECKDB does not examine disabled indexes. For more information about disabled indexes, see Disabling Indexes.

    DBCC CHECKDB is re-written in SQL Server 2005 and will NOT check the disabled indexes. So, it is NOT true.

    5) It has a flag not to run against nonclustered indexes.

    FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspx

    -- Check the AdventureWorks database without nonclustered indexes.

    DBCC CHECKDB (AdventureWorks, NOINDEX);

    GO

    So, it is true.

    6) It is comprehensive and validates the logical and physical integrity of all the objects in the specified database including tables, indexes, indexed views & statistics.

    I deliberately wanted people to get this incorrectly and thats why I added STATISTICS at the end of the end sentence. I saw only one reference on this on the CONNECT item and wanted to share this with every one. Before I knew about the connect item, I didn't know that CHECKDB will NOT check the STATISTICS.

    [font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

  • Thanks for the explanation. I missed that 'Statistics' was in the last answer. When I first read the answers I thought you were saying that is would fail against Tempdb. I mis-read that also. Not enough coffee I guess.

  • Sankar Reddy

    5) It has a flag not to run against nonclustered indexes.

    FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspx

    -- Check the AdventureWorks database without nonclustered indexes.

    DBCC CHECKDB (AdventureWorks, NOINDEX);

    GO

    So, it is true.

    Repeating myself:

    From Books On Line

    DBCC CHECKDB

    Runs DBCC CHECKALLOC on the database.

    Runs DBCC CHECKTABLE on every table and view in the database.

    Runs DBCC CHECKCATALOG on the database

    From DBCC CHECKALLOC.

    http://technet.microsoft.com/en-us/library/aa258809(SQL.80).aspx

    Note NOINDEX is maintained for backward compatibility only. All indexes are checked when executing DBCC CHECKALLOC.

    From Merriam-Webster Dictionary for ALL

    every member or individual component of

    So then Sankar Reddy what you are saying is that DBCC CHECKDB when invoking DBCC CHECKALLOC passes to it a an undocumented parameter so that DBCC CHECKALLOC does NOT perform as it states in BOL

    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]

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

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