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
GO
Step 2
set the database into single user mode using
– Change into single user mode
– 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
– Change from multiuser
ALTER DATABASE dbName SET MULTI_USER
Great share.
ReplyDeleteEach and every step is very useful. I am SQL database administrator and I fixed the suspect mode issue by following commands. Last week, my friend got the same situation so, I suggested these steps but it didn’t work for him. At last, he tried a SQL repair software (Stellar Phoenix SQL database Repair) and fixed the problem.