Hi,

My database when to suspect mode.... Now I changed it to emergency mode how can I recover it...

Need detail help... not that familuer with sql ...

Please help

 

Please rerfer the article SQL Server 2000 database in Suspect

 

Hi Irshard,

So below are the things that need to be done.

Put Database in SINGLE USER MODE in order to run DBCC CHECKDB

USE MASTER
GO
EXEC SP_CONFIGURE 'ALLOW UPDATES',1
Go
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS=32768 WHERE NAME='<DBNAME>'
GO
DBCC CHECKDB (<DBNAME>)
 
Run DBCC CHECKDB on problematic Database

   DBCC CHECKDB '<DBNAME>'

Check for Errors in DBCC CHECKDB output -- Those will be like below.

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:57265). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
 
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 0, page (1:57311). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
 
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. The previous link (1:57487) on page (1:56927) does not match the previous page (1:57506) that the parent (1:18380), slot 3 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. B-tree chain linkage mismatch. (1:57506)->next = (1:56927), but (1:56927)->Prev = (1:57487).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. The high key value on page (1:57201) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:57506).
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. Parent node for page (1:57205) was not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. Parent node for page (1:57206) was not encountered.
 
CHECKDB found 1 allocation errors and 7 consistency errors in table 'MSrepl_transactions' (object ID 2137058649).
CHECKDB found 30 allocation errors and 369 consistency errors in database 'TVXDistPHX'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TVXDistPHX ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
Server: Msg 8966, level 16, State 1, Line 1
Could not read and latch page (1:12120) with latch type SH. sysindexes failed.
DBCC execute completed. If DBCC printed error messages, contact your system administrator

From DBCC CHECKDB example on TVXDISTPHX database we saw problem on couple of indexes. We decided to rebuild those indexes and for that run below commands & then run DBCC CHECKDB.
 
USE <DBNAME>
GO
Exec SP_MsForEachTable @command1="print '?' DBCC DBREINDEX('?')"
GO
DBCC CHECKDB
 

After Index Rebuild task DBCC CHECKDB gives below errors.

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:57265). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:57266). The PageId in the page header = (0:0).
 
Server: Msg 8904, Level 16, State 1, Line 1
Extent (1:57624) in database ID 8 is allocated by more than one allocation object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (1:57624) is allocated to 'GAM' and at least one other object.
 
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 0, page (1:57311). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
 
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. B-tree chain linkage mismatch. (1:57506)->next = (1:56927), but (1:56927)->Prev = (1:57487).
 
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. The high key value on page (1:57201) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:57506).
 
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. The previous link (1:57487) on page (1:57230) does not match the previous page (1:57229) that the parent (1:57221), slot 8 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 5575058, index ID 1. B-tree chain linkage mismatch. (1:57229)->next = (1:57230), but (1:57230)->Prev = (1:57487).
 
 
CHECKDB found 1 allocation errors and 7 consistency errors in table 'MSrepl_transactions' (object ID 2137058649).
CHECKDB found 30 allocation errors and 369 consistency errors in database 'TVXDistPHX'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TVXDistPHX ).

From the DBCC CHECKDB output the minimum recovery that it recommend has potential to lose data (REPAIR_ALLOW_DATA_LOSS).

Run DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS.
 
USE MASTER
GO
EXEC SP_DBOPTION '<DBNAME>','SINGLE USER','TRUE'
GO
DBCC CHECKDB ('<DBNAME>',REPAIR_ALLOW_DATA_LOSS)
 

Now above things I provided as a example sake only.  But yes steps will be like that only. Put Database in SINGLE USER MODE and then run DBCC CHECKDB and based on output work accordingly. Link provided by SIVA above will help.

HTH

Hi Gursethi,

Thanks for all the details... I have put my Database in to Emergecy mode... where I cant come out of that....

when I try to recover... it takes long time... nothing happens

And one more

http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

If its a critical production database - Open a case with Microsoft Support.

If its for your learning - Start with paul Randals blogs.

• Proposed as answer by Tom Li - MSFTModerator Thursday, October 07, 2010 3:02 AM

First of all you need to use built-in function of Restore a Database Backup through SQL Server Management Studio.

Second step check a backup database, in all version of SQL Server you may find backup folder.

Read these item Recovery Toolbox for SQL Server 

More detailed information is available from http://www.sql.recoverytoolbox.com/