DBCC CHECKDB

Question

DBCC CHECKDB

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
July 30, 2013 at 9:46 pm

#395229

Comments posted to this topic are about the item DBCC CHECKDB

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks Steve

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Nice question,........

Thanks Steve 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

DivyaMohankumar SSC Rookie Points: 45 More actions · Answer 3

Good question.... I wish to Share some of the points:-P:

->DBCC CHECKDB - Checks the logical and physical integrity of all the objects in the specified database.

->DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.

->DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.

->DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 4

DivyaMohankumar (7/30/2013)
Good question.... I wish to Share some of the points:-P:
->DBCC CHECKDB - Checks the logical and physical integrity of all the objects in the specified database.
->DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
->DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
->DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online.

Good question. And thanks divya for this information. 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

karuppusamy.kk SSC Journeyman Points: 80 More actions · Answer 5

Hello Steve,

Please clarify my following concern about this question..

Why not DBCC CHECKIDENT?

Checks the current identity value for the specified table and, if needed, corrects the identity value.

Reference : http://msdn.microsoft.com/en-us/library/aa258817(v=sql.80).aspx

Regards,

K.K.Karuppusamy

sqlnaive SSCoach Points: 17437 More actions · Answer 6

DivyaMohankumar (7/30/2013)
Good question.... I wish to Share some of the points:-P:
->DBCC CHECKDB - Checks the logical and physical integrity of all the objects in the specified database.
->DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
->DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
->DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online.

Definitely a simple good question for a recap. And good one liners from DivyaMohan. 🙂

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 7

Michael Riemer

SSCertifiable

Points: 5190

July 31, 2013 at 1:32 am

#1637246

Great reminder question - thanks Steve.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 8

karuppusamy.kk (7/31/2013)
Please clarify my following concern about this question..
Why not DBCC CHECKIDENT?

The short and simple answer: because Microsoft didn't build DBCC CHECKDB to include CHECKIDENT.

The longer and more useful answer: DBCC CHECKDB is intended to test the integrity of the database. The errors it finds are the errors that can cause the database to go corrupt. If there are no corruptions in the database, DBCC CHECKDB will do nothing. If there are errors, if may attempt to repair them with as little side effects as possible.

Contrast that the DBCC CHECKIDENT. This doesn't test for corruption; it tests for specific situations in user tables that may be a sign of inconssitencies, but definitely not of corruption. If DBCC CHECKIDENT "repairs" issues it finds, it will have a side effect on the user data (i.e. it affects identity values to be assigned in the future).

Bottom line - DBCC CHECKIDENT is too different from the intended use of DBCC CHECKDB to be included in it.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

Hugo Kornelis (7/31/2013)
karuppusamy.kk (7/31/2013)
Please clarify my following concern about this question..
Why not DBCC CHECKIDENT?
The short and simple answer: because Microsoft didn't build DBCC CHECKDB to include CHECKIDENT.
The longer and more useful answer: DBCC CHECKDB is intended to test the integrity of the database. The errors it finds are the errors that can cause the database to go corrupt. If there are no corruptions in the database, DBCC CHECKDB will do nothing. If there are errors, if may attempt to repair them with as little side effects as possible.
Contrast that the DBCC CHECKIDENT. This doesn't test for corruption; it tests for specific situations in user tables that may be a sign of inconssitencies, but definitely not of corruption. If DBCC CHECKIDENT "repairs" issues it finds, it will have a side effect on the user data (i.e. it affects identity values to be assigned in the future).
Bottom line - DBCC CHECKIDENT is too different from the intended use of DBCC CHECKDB to be included in it.

+1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

Back to basics. Thanks for the question Steve.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Dave62 SSCertifiable Points: 7459 More actions · Answer 11

Nice question Steve!

So far, the most chosen wrong answer is: CHECKINTEGRITY. :hehe:

Which, of course is not a valid DBCC check. :w00t:

OCTom SSChampion Points: 11785 More actions · Answer 12

Dave62 (7/31/2013)
Nice question Steve!
So far, the most chosen wrong answer is: CHECKINTEGRITY. :hehe:
Which, of course is not a valid DBCC check. :w00t:

No it's not, but, it sounds really good for one. A nice red herring thrown in there by Steve. 🙂

However, there is an SSIS task named Check Database Integrity. 😉

TomThomson SSC Guru Points: 104773 More actions · Answer 13

It's a good question.

It's a bit shocking to see that so far out of just over 600 people about 55 people picked the non-existent and utterly implausible DBCC CHECKCACHE, and about 175 picked the equally non-existent although less implausible sounding DBCC CHECKFILEGROUP; and it seems amazing that about 120 people thought that DBCC CHECKIDENT was something that DBCC CHECKDB would do. So I hope that people will learn from this question.

Tom

Miles Neale SSChampion Points: 13147 More actions · Answer 14

L' Eomot Inversé (7/31/2013)
It's a good question.
It's a bit shocking to see that so far out of just over 600 people about 55 people picked the non-existent and utterly implausible DBCC CHECKCACHE, and about 175 picked the equally non-existent although less implausible sounding DBCC CHECKFILEGROUP; and it seems amazing that about 120 people thought that DBCC CHECKIDENT was something that DBCC CHECKDB would do. So I hope that people will learn from this question.

Tom, I was looking for DBCC ATTITUDE to see how I felt about the question but could not find it in the list. 🙂

Not all gray hairs are Dinosaurs!