Sometimes when connecting to the database, the database becomes inaccessible and you will get the following error message:
The error message indicates that the database is in SUSPECT mode. In this article I am going to show you how to create a suspect database and it’s repair solution.
Now I am going to tell you the scenario of Suspect database in SQL Server manually and how to repair it.
CREATING THE SUSPECT DATABASE
- Launch SQL Server Management Studio.
- I have created the database already named example, and I am going to fetch data from its table to check database accessibility.
- After fetching the data from the database table, close the connection to make the database SUSPECTED.
- Now go to the Services by running services.msc and stop SQL Server services by right clicking on it.
- Now I am going to corrupt the log file of example database. For this I am going to use hex editor.
- Launch Hex Editor. Open transaction log file that is example_log.ldf extension.
- We get the strings of that database. I am going to delete first four rows to make the log file corrupt.Save that corrupted file with CTRL+S.
- Again, Turn on the services of SQL Server.
- Connect to the database again.
- Fetch the data of your database to check whether the database goes into Suspect mode or not.
- When you fetch your data, you will get the following error message.
- Having low disk space.
- System failure.
- Improper system shut-down.
- Corrupted transaction log file.
- To repair your database from the suspect mode, you have to set your database in Emergency Mode by:
- Then permit single user mode to your database by:
- Then use below command to repair the database from the SUSPECT Mode.
DBCC CHECKDB (N ‘example’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
- The database has been successfully repaired, and get into the Single user Mode.Now setting database from single user mode to multiple user.
- Fetch your data from the database, you will finally get the recovered data.
This error message indicate that your database goes into the Suspect mode.
POSSIBLE REASONS FOR SUSPECTED SQL DATABASE:
Caution: Things you shouldn’t perform during SUSPECT mode :
When your database is in SUSPECT mode you cannot detach your database. You will get the following error, when you try to detach the suspected database. By doing this, you will loss all the possible chances of repairing a suspected database.
Msg 3707, Level 16, State 2, Line 1
Cannot detach a suspect or recovery pending database. It must be repaired or dropped.
This error will not allow your database to get detached because it is in SUSPECT mode.
Now comes to its Repair solution.
How to Repair SUSPECT DATABASE in SQL Server
Alter Database [example] Set EMERGENCY;
Alter Database [example] set SINGLE_USER;
Warning: Choose it as a last option as it leads to data loss.
In this blog section, I have discussed how to create a suspect database and how to repair SUSPECT database using Emergency mode. The blog discusses the possible reasons for Suspected database and things you should not perform when your database is in SUSPECTED Mode. You can also go for an alternate option like SQL Database Repair to recover your data.