DBCC CheckDB - what does it exactly do?

Question

Post reply

DBCC CheckDB - what does it exactly do?

Roger Abram

Hall of Fame

Points: 3683
More actions
December 1, 2008 at 7:04 am

#198243

Does the command "DBCC Checkdb" by itself actually perform minor repairs or do you have to add a repair switch to it?
Thank you.
Rog

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

CheckDB, when run without options, just checks for errors.

The best options for checkdb (when run as a regular integrity check) is with the ALL_ERRORMSGS and NO_INFOMSGS options.

If you get corruption, then decide how you want to fix it. In general, repair is not the preferred option. Rebuild the indexes if all the corruption is in non-clustered indexes or restore from a clean backup if the corruption's in the cluster/heap.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul Randal One Orange Chip Points: 29544 More actions · Answer 2

And if you want to know exactly what it does, see http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Complete-description-of-all-CHECKDB-stages.aspx

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Roger Abram Hall of Fame Points: 3683 More actions · Answer 3

Thank you for the insight. When I run it, I get the following message:

CHECKDB found 0 allocation errors and 1 consistency errors in table 'Freight_Rate' (object ID 917578307).

How would you handle a consistency error and what does it actually mean?

Roger

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

Depends on the consistency error - can you post the actual error message too?

Consistency errors are corruption in the database - nearly always caused by the I/O subsystem.

To recover without data loss, restoring from backups is nearly always the best bet. Dependong on what backups you have, and whether you can allow any data to be lost, backups may not be the best way to limit downtime. Usually DBAs strive to avoid data loss and so have a comprehensive backup strategy.

Do you have backups you can restore from?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Roger Abram Hall of Fame Points: 3683 More actions · Answer 5

This appears to be the more complete message:

Msg 2508, Level 16, State 3, Line 1

The In-row data RSVD page count for object "Freight_Rate", index ID 0, partition ID 60134411927552, alloc unit ID 60134411927552 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

There are 5119 rows in 40 pages for object "Freight_Rate".

CHECKDB found 0 allocation errors and 1 consistency errors in table 'Freight_Rate' (object ID 917578307).

No real backups.... don't know how long this has been going on.

Rog

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

That's no a serious problem.

As the output from CheckDB said:

Run DBCC UPDATEUSAGE.

No real backups.

Why not?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Roger Abram (12/1/2008)
No real backups.... don't know how long this has been going on.
Rog

I have to focus on the above, does this mean you don't have regular backups being taken of the database?

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

And this is a 2005 error message - be aware that this is a 7.0 + 2000 specific forum - there's a 2005 corruption forum too.

The updateusage issue is documented in the 2005 readme, upgrade notes, and in the first paragraph of the Remarks section of the Books Online entry for DBCC CHECKDB - always worth checking Books Online for problems you're seeing.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Roger Abram Hall of Fame Points: 3683 More actions · Answer 9

Sorry for the confusion... yes, there are regular backups. Just don't know how long this situation has occurred from within the database. So I wouldn't know where to start in terms of looking for a period before it existed.

Rog

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Roger Abram (12/1/2008)
Just don't know how long this situation has occurred from within the database. So I wouldn't know where to start in terms of looking for a period before it existed.

In this case, you can just run DBCC updateusage.

You should consider running checkDB regularly (weekly is good) so that you can pick up problems as soon as they occur.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass