How to Recover SQL Server Database in Suspect Mode: Reliable Approach

While working on SQL Server database, the issues related to this is common. But the situation becomes complicated when SQL Server Database is not accessible and shows message Database in Suspect Mode. When database goes into suspect mode the user is not able to access the database. The reason could be the corruption in database files, hardware failure, sudden shutdown etc. The result of which the user has to face is the data loss issue.
Now, the question arises, how to recover SQL Server Database in Suspect Mode? The user can get the answer to this question in the section given below.

In this blog, we are going to explain the complete situation i.e. why and how SQL Server Database goes into Suspect Mode and How to fix SQL database in Suspect mode.

“I was running SQL queries to add my colleague’s details on SQL Server database and my SQL Server database get restarted suddenly. To find out the reason, I checked out & found that there was issue related to my disks. When I tried to connect to my database, it was showing SQL error 945. I was not able to understand what to do next. Then I searched on the Internet and got to know that my database is in Suspect Mode. I tried to Detach and then Attach the file, but get failed. Does anyone know how to recover SQL Server Database in Suspect Mode. Please suggest me simple and reliabe method to fix this problem. Thanks in advance!”

SQL Server database is in Suspect Mode? Need a solution to fix it? Do not worry, you are landed on the perfect page. In the section given below, we are going to discuss an efficient and simple method to repair SQL Database in suspect mode. Additionally, we will also explain how to create a SUSPECT database, detach it and re-attach it.

Before moving towards the solution, let us find out the possible reasons that are responsible for the Suspect Mode of SQL Server database.

Database in Suspect Mode- Reasons

There are different reasons that are responsible for Database in Suspect Mode of SQL Server database. Some of them are listed in the below-mentioned section.

• Damaged Log files or data files
• Lack of disk space
• Improper shut down of SQL Server database
• SQL Server unable to complete operations such as rollback, roll forward etc.
• SQL Server crashed

How to Recover SQL Server Database in Suspect Mode?

In the section given below, we are going to state the complete situation how the error encountered and method to resolve it.

• • Create a SUSPECT Database
    1. First, create a simple database with a table and some random data
    2. Now, update any random transaction and force it to be written out to disk with a CHECKPOINT
    3. After that, in another window, simulate a crash using:

SHUTDOWN WITH NOWAIT;
Go

• • 1. Shutdown the SQL Server. Now I am going to corrupt the log file using Hex editor.
    2. Open the log file and change the strings in the editor.
    3. Now, restart SQL Server and run recovery on the table that you created. You will notice that recovery failed and database goes into the SUSPECT state

• • 1. After that, check the database status by

Select DATABASEPROPERTYEX (N’Test’, N’STATUS) AS N’Status’;

• • 1. Once the database is in SUSPECT MODE, the next step is to detach the database and then re-attach it to fix it.
  • Detaching the SUSPECT Database
    For detaching, you just need to put database in offline mode. Copy the files somewhere else. And then drop the database.
    Your database get successfully detached by SQL Server.
  • Re-attaching the SUSPECT Database
    For re-attaching the SUSPECT database the run sp_attach_db command, you will get Error Msg 5172.
    While detaching the SUSPECT database, error 3707 encountered. After that, when the user tried to re-attach it another error encountered that was error 5721. Now, the user can repair the SUSPECT database.

Recover SQL Server Database in Suspect Mode

• • To repair the SUSPECT database, the user has to follow the steps given below:
    1. First, set the database into emergency mode using the SET EMERGENCY command

Alter Database (DB_NAME) Set Emergency;

• • 1. Now, you have to set the single user by using SET SINGLE_USER command

Alter Database (DB_NAME) Set Single_User;

• • 1. Finally, repair the database by using REPAIR_ALLOW_DATA_LOSS

DBCC CHECKDB (N’DB_NAME’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGWS, ALL_ERRORMSGS;

After implementing the above steps the user will be able to repair the database.

Making Smart Move:

There could be the possibility that Repairing SQL Server database in Suspect Mode could fail. However, if the database file is severly corrrupted, the possibility of failure is quite high. For that case, user can go for a reliable and an automated approach that is SQL Database Repair Tool to fix SQL database in SUSPECT Mode. With this utility, the user will be able to recover SQL Server Database in Suspect Mode in a hassle free way. Moreover, it will eliminate all the issues related to corruption in SQL Server database.

Final Words

The blog discusses How to Fix SQL Database in Suspect Mode. The database becomes inaccessible when database goes in suspect mode. You can repair SQL database in Suspect mode by performing manual tasks which are described above. For quick approach, you can also go for third party utility to recover SQL Server database in suspect mode.