SUSPECT MODE

 DATABASE GOES TO SUSPECT MODE IN SQL SERVER

 Generally, Database moves to SUSPECT State due to following Reasons

 §  Database is corrupted

 §  ldf file is corrupted

 §  Disk Hardware Failure or Lack of Disk Space

SQL Server does not have sufficient memory to Start SQL Server  

                                        

         HOW TO RECOVERY DATABASE FROM SUSPECT MODE

  

IN SQL SERVER 2000:  

                       

  STEP 1) First we have to check the .MDF and  .LDF files and their location.  

    By using the query                                                          select * from Sysaltfiles  or      

                             sp_helpdb 'Db_name'

If the .mdf and .ldf files are correct, then check for the Disk space issues. 

STEP 2) If the .ldf has moved from its physical location path or damage then

               Stop the SQL SERVER Services on the server using services.msc OR

               By using  SQL SERVER Service Manager

STEP 3) Rename Physical database log file name. i.e DBName_log1.LDF

STEP 4) Execute Following query from query Analyzer.

                      EXEC sp_configure ‘Allow updates’,’1’

                                     Reconfigure with override

STEP 5) Set the Database to Emergency mode by executing the following query

                     Update master.dbo.sysdatabases

                     Set status= 32768-- (Emergency mode)

                    Where  [name]=’Db_Name’

                    Go

Once the Database is set to EMERGENCY MODE it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to acess it.

STEP 6) Bring the Database to SINGLE_USER mode by using the following query

                       

                            Exec Sp_dboption’DB_NAME’,’Single_User’,’True’

STEP 7) Rebuild the Transcation Log file of the Database using the below consistency check

                      DBCC REBUILD_LOG(‘DB_NAME’,’PATH’)  

STEP 8) Run DBCC CHECKDB as follows to ensure that the database is free from any sort of corruption.

                                                             DBCC CHECKDB(DB_NAME)

STEP 9) If DBCC CHECKDB doesn't print any error message then it means that we are successful.

STEP 10) Go to Database properties Uncheck the DBO User Only Restricted Access option                                                                OR

             Exec Sp_dboption’DB_NAME’,’Single_User’,’False’

STEP 11) Execute Following query from query Analyzer.

                      EXEC sp_configure ‘Allow updates’,’0’

                                 Reconfigure with override      ---To stop the changes

IN 2005/2008:

• This can happen following things like hardware failure, power outages, database files being locked by the Operating System (Anti-Virus, backup software etc.) or actual corruption of the database.
• Attempting the repair procedure below is really a last resort.  If you have good and recent backups, then if at all possible I would perform point-in-time recovery of the database concerned, as this emergency repair can (as the name suggests) result in data loss.
• If you’ve gone through all of this like I once did though, and all other avenues failed, here are the steps that fixed it for me:

Step 1)

Never detach a suspect database it end up without re attach .as it is a Suspect database we can’t take Backup of it. take the database into EMERGENCY MODE

– Take the database into Emergency mode

                            Exec Sp_resetstatus'Db_Name'  

                            ALTER DATABASE [Database Name] SET EMERGENCY
                              GO

Step 2

set the database into single user mode using
– Change into single user mode

                            ALTER DATABASE [Database Name] SET SINGLE_USER with  Rollback Immediate

Step 3

Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS   ---- by using this command all the open transactions will be deleted AND can cause some data to be lost!.

                        DBCC CheckDB (<[Database Name]> , REPAIR_ALLOW_DATA_LOSS) 

Step 4

when the database repair is done set the database in multi user mode using
– Change from multiuser

                       ALTER DATABASE dbName SET MULTI_USER

Know connect to the database to check the database server is online or not.