Summary: This post is for user who is looking for solution to recover SQL Server Database from Suspect Mode. This post describes steps to recover database from suspect mode by using T-SQL Commands in SSMS. Also, it describes other ways to recover using recovery tools.
What is Suspect Mode?
In simple terms : Database goes into Suspect Mode when When SQL Server tries to initiate database and fails because of certain reasons. Mostly it can be System failure or disk issues or file inaccessible.
Here are detailed reasons when and how it can happen.
What causes databases go into Suspect Mode?
I have multiple case scenarios when user was using the database or application on the SQL database which was running perfectly fine, and all of sudden in the next morning database goes into suspect mode.
In such case I ask few questions in this sequence and user finds the cause of issue in my questioner.
Here are few reasons when you may find database in Suspect mode.
System Failure : System or Windows failed or corrupted accidently
Improper System Shutdown : Windows did not shutdown properly last time.
Low Disk Space : Low disk space on the machine, which is not allowing access mdf / ldf files.
Missing Log File : This may happen when Log file is not available
Files inaccessible : Inaccessibility of MDF or LDF file set it to Suspect Mode
Database File Corrupt: There are many reasons of corruption of database file. In few cases of database corruptions given solution below is helpful. In other cases you have to find out what is cause of corruption of database file.
How to Recover using SQL Command?
Step 1: Set database to Emergency Mode
Start SQL Server Management Studio(SSMS) or SQLCMD tool (if you do not have SSMS) and Open New Query window
Turn off Suspect flag using this command
EXEC sp_resetstatus 'yourdbname';
Set database to Emergency
ALTER DATABASE yourdbname SET EMERGENCY
Step 2: Run Consistency Check using DBCC
DBCC CHECKDB ('yourdbname')
Step 3: Bring database into Single User mode
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Step 4: Backup database for safe side of data loss
Step 5: Execute database repair with allow data loss
DBCC CHECKDB ('yourdbname', REPAIR_ALLOW_DATA_LOSS)
Step 6: Set database to Multi User
ALTER DATABASE yourdbname SET MULTI_USER
Lets collect all the steps into one query block and this is how all steps look like.
--Set Database to Emergency ALTER DATABASE yourdbname SET EMERGENCY; -- Check database consistancy DBCC CHECKDB (yourdbname ); -- Set user to Single User ALTER DATABASE yourdbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- Rapair database with Allow Data Loss DBCC CHECKDB (yourdbname , REPAIR_ALLOW_DATA_LOSS) -- Set database to multi User ALTER DATABASE yourdbname SET MULTI_USER
Recover using Recovery Tools
In most of cases, use gets success recovering database is into normal condition. However, you may not be able to recover database from Suspect Mode to Normal mode if database files are badly corrupted. In such situation we can help from the specially designed Database Recovery Tools like Stellar and EaseUs MS Recovery Tool.
I have used Stellar Database Recovery Software many times to recover database from critical condition.
Suspect mode is not a dangerous status of database but a status which could not resolved by SQL Server itself. We also understood that it can be resolved by running bunch of commands.
See Also :