How to Recover or Repair Suspect Database in SQL Server
Suspect database in SQL Server is not very common issue but there are times we face it because of various reasons. Such reasons can cause a database to go in suspect mode and become inaccessible for end users. There are some steps we can follow to fix this issue like manually bringing database online, checking any databases files are missing or not etc. If nothing will work then we will run DBCC CHECKDB command to fix suspect database issue. You should be cautious while using this command as you might have data loss while running DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS. I will explain these steps in this article.
Reason for Database Suspect Mode in SQL Server
Here, i will explain the list of possible reasons which can cause your database to move into SUSPECT mode. Below is the list of such reasons.
- Improper shutdown of the database or database server.
- Hardware Failure.
- Corruption in database file/s.
- There is not enough space available for the SQL Server to recover the database during startup.
- Inaccessible database files or Insufficient memory.
- Missing any Database files
- Sudden Power Outage
- System Failure
- Database files drive letters mistyped/changed during any maintenance work.
If your database is showing in SUSPECT mode, you should check any reason from above list for this issue. Next, i will show you how to recover or repair suspect database in SQL Server.
SQL Server Suspect Database Fix
This section will explain how to bring database ONLINE from SUSPECT mode in SQL Server. To troubleshoot and repair suspect database, first you need to analyze your database server. You need to check whether all database files are in same location where they should be. if not, get the reason why files have been moved to another drive and by whom. You can move the files to previous location or you can change/update the location of these database files in SQL Server catalogue view using ALTER command. Additionally, you can also check whether all databases files are safe and none of these database files are missing from their respective location.
Related Articles:
- How to get Last Restore Time of your database?
- Understanding Data Files, Pages & Extents
- Understanding SQL Server Recovery Models
Sometimes, database files drive letters got changed as human error during some maintenance work. If this is the case, don’t do anything on the database server and ask your windows or platform team to revert the drive letter name where your database files have been placed to the correct name. You can also do this change if you are authorized to do this.
Once you have checked the drive letters and there is no change in it then you can try to run below SQL statement to bring your database online.
--Change DBNAME with your database name that is showing in suspect mode. Alter database DBNAME SET ONLINE.
Sometimes databases come online by running above Alter statement if there is no serious issue with the database.
Get information about power outage or any hardware or system failure. If this is the case then first we need to address these issues and then we can recover the databases. If your database is HA enabled and you have system failure then you can use standby database for your business transactions.
If nothing works and all above steps are failed to bring suspect database ONLINE then we will run DBCC CHECKDB T-SQL statement to get all database corruption errors due to which database went in suspect mode:
DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGSAbove command will thoroughly check database integrity and return issues and corruption errors in the output section. You can read and review output of above DBCC CHECKDB command to understand the corruption or integrity issue in database.
Once you have enough information about errors then your next step would be Repair this suspected database. If error is showing data files missing then check your data file drive connectivity from storage side if it’s missing then you don’t need to do anything just contact someone from storage or one who manages storage in your environment and ask them to check data file drive is healthy or not. If storage is also ok then we will repair our database using below t-sql codes:
ALTER DATABASE [DBNAME] SET EMERGENCY
go
ALTER DATABASE [DBNAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
go
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
ALTER DATABASE [DBNAME] SET Multi_USER;
Put your db name in place of [DBNAME] in above script. Above alter statement will change database into emergency mode and then repair the corruption present on data pages.
If your database does not come online post running above Alter statement then only option left is to restore your database with the copy of latest backups.
Note: REPAIR_ALLOW_DATA_LOSS is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.
I hope you like this article. Please drop your questions and feedbacks in below comment section. You can also follow our Facebook page and Twitter handle to get latest updates.
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
