DBCC Database Consistency Checker

Check the health of your database data pages, index pages, repair, check corruption.

Monitor performance, Tune performance

Configure Database/server parameters

Debugging and Tracing.

Health Of Database / Repair Corruption in Database.

Below option checks both logical and physical

integrity of database.

DBCC CHECKDB('<db_name>') with no_infomsgs

DBCC CHECKDB('<db_name>') with physical only.

with physical only causes dbcc to check the physical

structure only.

If you get error like "

Table error: Object ID 30592645, index ID 0, partition ID 652640102126454, alloc unit ID 65264654654654

(type In-row data), page (1:64499). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.

CHECKDB found 0 allocation errors and 2 consistency errors in table 'testdb' (object ID 30592645).

CHECKDB found 0 allocation errors and 2 consistency errors in database 'testdb'.

Now here we will get the information about the corrupted block

DBCC Traceon(3604). To Redirect output to screen for rest of the dbcc commands.

DBCC Page , Lets you see the contents of the page

Syntax

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

If you find page id to be (1:33321) its file no 1 and page 33321

Printopt has following meanings

0 - print just the page header

1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)

2 - page header plus whole page hex dump

3 - page header plus detailed per-row interpretation

Now here we will execute

dbcc page ('testdb',1,64499,3)

Recover page from backup.

restore database <db_name> page='1:4211' from disk='d:\<db_name>.bak'

with norecovery;

From here you could apply the same to other blocks.

No we will have a look at the automatic repair options

Just check the output of the DBCC CHECKDB output.

It will show us the minimum repair level.

DBCC CHECKDB('DB NAME', REPAIR_REBUILD)

REPAIR_ALLOW_DATA_LOSS. This will try to repair the error

but could just modify some pages as if they never existed.

DBCC CHECKDB('<db_name>',REPAIR_ALLOW_DATA_LOSS)

Do run the DBCC CHECKCONSTRAINTS to check the referentail integrity.