SQL SERVER - DBCC CHECKDB Introduction and Explanation - DBCC CHECKDB Errors Solution

Feed on: Posts; Comments

SQL SERVER - DBCC CHECKDB Introduction and Explanation - DBCC CHECKDB Errors Solution

November 13, 2007 by pinaldave

DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database. If DBCC CHECKDB ran on database user should not run DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG on database as DBCC CHECKDB includes all the three command. Usage of these included DBCC commands is listed below.

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.

Along with above three DBCC commands it also runs following two tasks to check the validity database (physical as well logical) i.e. validates the contents of every indexed view in the database and Validates the Service Broker data in the database.

If database DBCC check has returned any errors, the best solution is to RESTORE DATABASE from BACKUP. (Additional reading : SQL Backup and Restore). There is additional keyword REPAIR with DBCC CHECKDB which can be used to repair database but it is not recommended. I will write additional articles on this subject.

Reference : Pinal Dave (http://www.SQLAuthority.com) , DBCC CHECKDB

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 4 Comments

4 Responses to “SQL SERVER - DBCC CHECKDB Introduction and Explanation - DBCC CHECKDB Errors Solution”

on January 1, 2008 at 6:06 pm Asher

Hi Pinal!

Can dbcc be run from a cmd prompt?

How can it be run?

sorry for my ignorance but I’m new to SQL.

Thanks,

Asher.
on May 2, 2008 at 1:40 pm Mangesh

how to execute DBCC command on linked server.
on May 9, 2008 at 5:25 am John

Hi Pinal,
We have a restore problem.

We have SQL 2003 sp4 and suddenly no one can restore databases. Even newly created ones will not restore. We get the error msg “Could not open FCB for invalid file ID2 in database ‘databasename’
The EVENTID is 17052 in Event Viewer. In there it says Error:5180, Severity:22, State:1″
Any idea where I should look to fix this or how to fix it? Thanks, John
on September 17, 2008 at 11:31 pm Priya

Hi,

To check the consistency of SQL 2000 and 2005, can we generate a DBCC report on both the databases and validate it line by line?

Will that be the only possible solution?

Or we got anything else?

Appreciate any help!

Thanks,
Priya