DBCC CHECKDB

  • SQLDCH

    SSChampion

    Points: 11494

    SQLRNNR (8/20/2014)


    This question highlights another great example of bad (conflicting) documentation.

    Within the same documentation one will find both of the following statements.

    DBCC CHECKDB does not examine disabled indexes.

    And

    Unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical consistency checks on a single table and on all its nonclustered indexes.

    That last one is stated twice for good measure.

    SQL 2012 Version

    http://msdn.microsoft.com/en-us/library/ms176064(v=sql.110).aspx

    For good measure, the document is nearly the same for SQL 2014 (other areas changed but those statements were not).

    http://msdn.microsoft.com/en-us/library/ms176064(v=sql.120).aspx

    Even better is that the 2014 document has information regarding compatibility 90 which is not supported in SQL 2014.

    You can read a bit about the compatibility levels here[/url].

    Good catch Jason. I took that second statement to mean "..all it's <enabled> non clustered indexes..".

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • SQLDCH

    SSChampion

    Points: 11494

    TomThomson (8/20/2014)


    Nice question. 😎

    But I have one niggle :hehe:: you can't run that command against tempdb, because it is specifically acting of the database 'myDatabase' :laugh::crazy:; but it was absolutely obvious what you meant to ask so it's hard to see why so many people got that wrong :-P:laugh:. Unless of course they believe in being secure so that they normally log in as a user who doesn't have the permissions needed to run dbcc checdb against tempdb and think that QotD should be aimed at people who behave securely.

    But anyway, any other choice for the second correct option was obviously wrong :w00t:, so even security lovers should have gotten it right.

    And good point by you, Tom. Thanks for realizing the intention of the question and answers. I thought I finally had a rock solid question of the day!

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Ken Wymore

    SSCoach

    Points: 16391

    Answered too quickly on this one. I knew it could be run against tempdb but ignored that for some reason. Had I thought about it for another second then I would have second guessed my other choices and actually read the entire MSDN article! 😀 Oh well, good question.

  • Hany Helmy

    SSChampion

    Points: 13321

    SQLRNNR (8/20/2014) For good measure, the document is nearly the same for SQL 2014 (other areas changed but those statements were not).

    http://msdn.microsoft.com/en-us/library/ms176064(v=sql.120).aspx

    That helped, thx

  • Hany Helmy

    SSChampion

    Points: 13321

    Never used TABLOCK option with DBCC before, always using:

    DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS;

    DBCC CHECKDB WITH PHYSICAL_ONLY; -- Best Practice

    So I had to read the documentation carefully, then answered correctly, I am one of the luckiest 22% 🙂

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question. Thanks for sharing

  • SQLRNNR

    SSC Guru

    Points: 281210

    Hany Helmy (8/21/2014)


    Never used TABLOCK option with DBCC before, always using:

    DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS;

    DBCC CHECKDB WITH PHYSICAL_ONLY; -- Best Practice

    So I had to read the documentation carefully, then answered correctly, I am one of the luckiest 22% 🙂

    I'd be a bit careful about calling Physical_Only a best practice. It is a check to be run on very busy systems with the full check to be run less often on that system. But the full check still has to be run on occasion somehow.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 16 through 22 (of 22 total)

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