DBCC CHECKDB

Question

DBCC CHECKDB

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

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

SQLDCH SSChampion Points: 11627 More actions · Answer 1

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: 11627 More actions · Answer 2

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 SSCoach Points: 17191 More actions · Answer 3

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: 13921 More actions · Answer 4

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

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

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% 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

Umer Akram

SSC-Insane

Points: 22209

August 21, 2014 at 1:35 am

#1739785

Nice question. Thanks for sharing

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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