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 EMERGENCYThis 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 |
Database Technologies > MSSQL >