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.
- 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.
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.