What is WARNING: Database <db-name> is SUSPECT? You have probably seen a databases marked as SUSPECT in SSMS and you don’t know what it represents. Read below article to find out more details about this!
What is WARNING: Database db-name is SUSPECT?
Sometimes we can be faced with a situation which is critical, due to our SQL Server database going into Suspect Mode. At that point, the database cannot be used anymore and no work can be done.
The main reason why the database goes into suspect mode is because the primary file group has been damaged and the database cannot be recovered during the startup of the SQL Server.
Also, the database can get in the SUSPECT state for multiple other reasons, which can include:
- a system malfunction which might be caused by an improperly shut down of the database server
- a damaged LOG file or a damaged MDF file
- in case there is no more space on the Disk
- SQL Server is unable to complete a roll forward or rollback operation
- limitations in terms of space for FAT32 file systems and other reasons
In order to repair the database and recover from this state, you will have to do the following steps:
- We will start by changing the status of the database by executing the sp_resetstatus stored procedure
EXEC sp_resetstatus 'TestDB'
- Set the database to “Emergency” mode
ALTER DATABASE TestDB SET EMERGENCY;
- Check the database for any possible inconsistencies
- In case you encounter any errors during the DBCC phase then put the database immediately in SINGLE USER MODE with the help of the query below:
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
- Also, at this point, for safety reasons we recommend that you make a backup of the database
- After you finished making a backup of the database you should proceed to run the following query. But remember that while using the following query, with the REPAIR_ALLOW_DATA_LOSS parameter, you are executing a one way operation which will repair your database but all actions that are made during this process cannot be rolled back. There is no solution to get back to a previous state of the database, which is why we strongly recommend that you make the backup we suggested at the previous point
DBCC CheckDB ('TestDB', REPAIR_ALLOW_DATA_LOSS);
- The last query you should run is to set the database into MULTI USER mode
ALTER DATABASE TestDB SET MULTI_USER;
- The final step is to refresh your database server and check that the connectivity to your database is working. After this point users should have the possibility to connect to the database. In case there is any data loss you can restore the database which you previously backed up