How to fix SQL Server database suspect status

In case our SQL Server database can't access, and telling you that it was in suspect status:

Common reasons for the state are:

• The system cannot open the device where the data or log file is located
• The specified file was not found during the creation or opening of the physical device
• SQL Server crashes in the middle of the transaction
• Unable to access data or log files when going online due to anti-virus software installed
• Database server is illegally shut down
• Insufficient disk space cannot be written
• SQL cannot complete rollback or roll forward operation
• Database files are locked by third-party backup software

How to solve

Find the cause of the problem first. Please go back to the previous step and check if it is caused by the above reasons. For example, there is not enough disk space.

For the root cause, we must first solve the fundamental problem and then repair the database.

include:

• Check if the system is updated, whether there are serious security risks, whether there is hacking
• Check if the system power is stable
• Check if the disk space is sufficient
• Check if the database file has read and write permissions to the database process
• Check if third-party anti-virus software is installed
• Check if third-party backup software is installed
• Try to mount the database file to another computer

If you have solved the underlying problem, you can follow the steps below. Note that before starting, if the database is a virtual machine, a virtual machine snapshot is highly recommended.

10 step repair method

1. Open SSMS

Open SSMS and connect to the failed SQL Server instance.

2. Preparing to execute SQL

Find the database for the Suspect state, and be careful not to expand it. Just create a new query.

3. Set the database to a state of emergency

Execute the following SQL

EXEC sp_resetstatus database_name;
ALTER DATABASE database_name SET EMERGENCY

This sets the database to an emergency to allow for further fixes.

4. Running an error scan on the database

Execute the following SQL

DBCC CHECKDB (database_name)
DBCC CHECKCATALOG (database_name)

This will run a fault scan on the current fault database. You may see a lot of failures. These errors can be checked in turn. Note that this step will not fix any errors.

5. Preparing to fix

To run a database repair script, you must set the database to be a single user. This can also prevent other people who might be in the process of repairing from accidentally coming in.

At the same time, we want to roll back the most recent transaction, because the last transaction must have failed, and it is likely that the last transaction caused the database failure.

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

6. Run the fix

DBCC CHECKDB (database_name, REPAIR_FAST) -- quick fix
DBCC CHECKDB (database_name, REPAIR_REBUILD) -- reset index
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS) -- Allows correction of missing data
DBCC CHECKALLOC (database_name, REPAIR_REBUILD) -- Fixed allocation problem
DBCC DBREINDEX (database_name, REPAIR_REBUILD) -- fix index problem

The above code will try to fix the database error. If you are experiencing obstacles during execution, please check the chapter on high frequency issues at the end of the article.

Note that in the above code, REPAIR_ALLOW_DATA_LOSS indicates that data is allowed to be discarded in order to repair the database. The sixth step may take a long time, and it usually takes several hours for a database of around 30GB. Need to wait patiently.

Running the above SQL may not be completely fixed. You can use the following SQL to fix the error multiple times.

DECLARE @Number INT = 1 ;
While @Number < = 10
Begin
PRINT @Number;
SET @Number = @Number + 1 ;
DBCC CHECKDB(database_name, REPAIR_ALLOW_DATA_LOSS)
End

7. Re-allow multi-user connections to the database

At this point, the database has been restored. We can re-allow multiple connections to the database.

ALTER DATABASE database_name SET MULTI_USER

8. Back up now

At this point, the database has reached a usable state. The database should be backed up immediately to export a copy of the available data state. If the database is a virtual machine, it is recommended to take a snapshot at the same time as the backup.

At the same time, it is recommended to restart the entire database server and check if the database is still normal after the restart. This is to avoid the possible cause of repeated database leaks.

9. Checking the data

After the available data is available, we can check if the current database state is a newer state.

10. Fix other errors

At the end of the above process, although most of the data is already available, there are still more errors in the database.

You can view these errors using the following SQL:

DBCC CHECKDB (database_name)

If you want to fix this part of the error, you can try to back up and then restore the backup, which may solve this part of the problem.

FAQ

When the database is in a single-user state, we are unable to leave the current connection. Because once we leave, there may be other connections directly occupied.

In this case, we must manually kill the other connections that were rushed in, ensuring that we are the only user who is operating the database.

Methods as below:

Execute the following SQL first

Select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  From sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 Where d.name = 'testdb01'
Go

You will see a list of the output's sessions and find out the SPID of the incoming connection. For example, the SPID is 51.

Execute the following SQL

Kill 51
Go

At this point, we can continue to execute SQL on this single-user database.