How to Recover MS SQL Database from Suspect Mode?

Hi Friends,

Hope you are doing well.

In this post, I will show you how to recover your MSSQL database from Suspect Mode.

As you know there are different types of modes of the MSSQL database in MSSQL studio, like Online, Offline, Suspect, Emergency, etc. So a database is always in one or the another mode. If we want to run a SQL query and the site is online, there the database is in Online mode. If we want to dettach and attach database, we are making this as Offline and then Online respectively. If we want to perform certain tasks, when the database is not accessible, we use Emergency mode. Likewise, the database will be in a specific mode, depending on the situation.

What is Suspect mode of a database?

When connecting to a SQL database server, if you find a message indicating that the database is in Suspect mode, it means server suspects that the primary filegroup of the database is damaged/corrupted. In such case, you will not be able to access the database, you will not be able to execute a normal SQL query and your site will show database error.

Possible reasons for a database in Suspect mode:

An important fact is that when a SQL server starts up, it attempts to acquire an exclusive lock on the server’s device file. So if the device file is being used by another process or if it is found to be missing, then SQL server starts displaying errors. Possible reasons for such errors are given below:

1. The system failed to open the device where the data or the log files are present.
2. The database could have become corrupted.
3. There is not enough space available for the SQL server to recover the database during startup.
4. Cannot access the data or log files of the database while coming online, due to the installed antivirus in the system.
5. SQL server crashed or restarted during a transaction which result in corrupting the log files.
6. The database cannot be opened due to inaccessible files or insufficient memory/disk space.
7. The database server was shut down improperly.
8. Database files are being held by the operating systems, third party backup software, etc.

Steps to resolve the SQL server Database Suspect Mode error:

1. 1. Open SQL Server Management Studio and connect to your database.
   2. Select the New Query option. You can get this from Menu bar present in the SQL server management studio or you can get this by right clicking on the Suspected database and Select New Query.
   3. Now first execute the below command to turn off the Suspect flagged database and enter into Emergency mode.  
      EXEC sp_resetstatus [YourDatabase];
      ALTER DATABASE [YourDatabase] SET EMERGENCY
   4. Now perform a Consistency check on the database.  DBCC checkdb([YourDatabase])
   5. Bring the database into the Single User Mode and roll back the previous transactions.
      ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   6. Take a complete backup of the database. This will help if any data loss is there during recovery.
   7. Attempt the Database Repair allowing some data loss.   DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
   8. Bring the Database into Multi-User mode.
      ALTER DATABASE [YourDatabase] SET MULTI_USER
   9. Refresh the database server or database and verify the status of the database. It will be now Online and no longer in Suspect mode.

Now you can execute your SQL query and site will also be accessible/online.

Hope you like this post. If you have any further question, feel free to comment in the Comment section given below. I will be more than happy to help you.

Thank you and have a nice day. 🙂

My name is Shashank Shekhar. I am a DevOps Engineer, currently working in one of the best companies in India. I am having around 5 years of experience in Linux Server Administration and DevOps tools.

I love to work in Linux environment & love learning new things.

clicknsharephotography.com/

Powered by Facebook Comments