What is WARNING: Database <db-name> is SUSPECT?

You have probably seen databases marked as SUSPECT in SSMS and you don’t know what it represents.

Read the 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 that 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 that the primary filegroup 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:

  DBCC checkdb('TestDB');

• 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 that 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 the 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