DBCC Dealing with Corruption

Database Corruption.

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;

Emergency Mode.

ALTER DATABASE test_db SET EMERGENCY

This allow System administrator to access to the data

it could be reviewed and transferred to another proper database

When you execute the statement SQL Server will shutdown the database

and restart the database without recovering it.

But good thing is that you could view database objects, db will be in read only mode

ALTER DATABASE TEST SET SINGLE_USER

DBCC CHECKDB('DB NAME', REPAIR_REBUILD) // Will try to repair

DBCC CHECKDB('<db_name>',REPAIR_ALLOW_DATA_LOSS) // This require db in single user mode

//But data could be lost in here

Above if the command is succesfull it will db online.

If above doesn't work, restore from backup. If no backup. God Bless You