SQL Server Suspect Database
As we know that the data is a vital entity for everyone. Suppose you are working on your SQL Server database but an error occurs and database display a message on the screen that your database is in suspect mode. In such situation you have to choose the best method to recover the database. At this time you will not be able to connect the SQL Server database.
So it is very essential that the databases must be well maintained because that care is better than cure and we can escape to face the critical problem.
Reasons For Database Suspect Mode In SQL Server
- Operating System is unable to find the specified file.
- The database is unable to open the device where the LDF files or other files are stored.
- MS SQL Server performance decreases abnormally and restarts while the transaction is being processed, it may be a reason related to the corruption of log files.
- Unexpected SQL Server shutdown, power failure or a hardware failure could be a reason of SQL Server suspect database.
Error messages when a SQL Server Database is in SUSPECT mode:
Error: 9003, Severity: 20, State: 9 - The log scan number (178956:14:2) passed to log scan in database is not valid.
Error: 3414, Severity: 21, State: 1 - An error occurred during recovery, preventing the database (database ID 21) from restarting.
SQL Server Suspect Database Recovery
Manual method to repair SQL Server suspect database using SQL Server Management Studio
Step-1:Connect to the database using SQL Server Management Studio via Server Administrator Credential and find out the suspected database.
Step-2: Reset the database state of the SUSPECTED database using the T-SQL script.
SQL> execute sp_resetstatus ‘test_dr’;(dr: Disaster Recovery)
This T-SQL statement turns off the suspected flag of the respected database. After the execution of this query the database gives a warning message.
Step-3: In the third step you need to setup the database in Emergency mode. After this the database changed into read_only state and besides having the system admin privilege no other can access the database.
SQL> alter database db_name> set emergency;
Step-4: After executing this query the database goes from suspected mode to emergency mode. Now perform the consistency checking operation using the T-SQL Script.
SQL> DBCC CHECKDB database_name
This query checks the entire database accuracy; consistency; and checks the correctly storing and retrieving of the data. It also checks the correctness of the data provided by the database.
Step-5: Now set the database to the “single user” mode using the T-SQL statement “rollback immediate”.
SQL> alter database Database_name set_single_user with rollback immediate;
The above-given query will check the rollback if any are available, then let it go into the single user mode.
Step-6: Next step is to use the DBCC CHECKDB with repair allow data loss.
SQL> DBCC CHECKDB database_name repair_allow_data_loss
Step- 7 Now you can change the access permission of the database and makes it accessible to others. To do this, execute the following alter database query.
SQL> alter database database_name set_multiuser
After this perform the consistency check operation to insure that the repaired database is currently working in good condition or not.
The above explained manual method recovers the suspect database, but there are some limitations of this manual method to perform SQL Server suspect database recovery which depends on the corruption level of the database. So in such situations using third party commercail tool like MDF file recovery software can be helpful to overcome such situation.
Conclusion
In this article we discussed about the reasons for database suspect mode, and how to recover SQL Server database from suspect mode. Sometimes databases can be highly corrupted and having lot of critical issues, then the manual method of suspect database recovery may fails to recover the database.
EmoticonEmoticon