There are times when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. In such a scenario, you will not be able to connect to the database to read and write data. This article outlines the steps which you need to follow to recover your database which is marked SUSPECT.
Primary reasons when an SQL Server Database is marked in Suspect Mode
- System Issues
- Transaction Log File is Missing
- SQL Server Crash
- Database Files are inaccessible
- SQL Server Database Operation Failures
- Due to Improper shut down of SQL Server System
- Due to Sudden Power Outage
- Low Disk Space Issues
- Hardware Failure
The steps mentioned in this article works on SQL Server 2005 and all higher versions.
Steps to Fix the SQL Server Database Suspect Mode Error
Step 1: Bring Database Online in EMERGENCY MODE
Step 2: Perform Consistency Check Using DBCC Command DBCC CHECKDB
Step 3: Bring the Database in SINGLE_USER Mode to ROLLBACK TRANSACTION
Step 4: Take a Full Backup of the User Database which was marked Suspect Before
Step 5: Execute DBCC CHECKDB WITH REPAIR ALLOW DATA LOSS (Caution: It will result in Data Loss)
Step 6: Once the above command has executed successful. Bring the Database in MULTI_USER Mode for normal read and write operations
If you have database backups which allows you Point in Time Recovery then it is always recommended to restore your database from the available SQL Server Database Backups. However, do consider you have enough space available on the server to restore the database.
Steps by Step Guide to Repair Suspect Database in SQL Server are:
1. Execute the below mentioned TSQL code to identify all the databases which are marked as SUSPECT.
USE master GO SELECT NAME,STATE_DESC FROM SYS.DATABASES WHERE STATE_DESC='SUSPECT' GO
Due to a hardware failure one of our database namely BPO was marked SUSPECT when the SQL Server came back online. Already due to the hardware failure we had downtime for more than two hours and adding to that when the server came back online our mostly critical database was marked as SUSPECT.
2. Open the latest SQL Server Error Log and check for errors logged for the database which is marked as suspect. You can open SQL Server Error Log by expanding Management Node -> SQL Server Error Logs. In my server I could find below mentioned entries in SQL Server Error Logs.
Sample Error Messages within SQL Server Error Log when database is marked as SUSPECT
Starting up database 'BPO'. Error: 9003, Severity: 20, State: 9. The log scan number (189624:16:2) passed to log scan in database 'BPO' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. Error: 3414, Severity: 21, State: 1. An error occurred during recovery, preventing the database 'BPO' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. CHECKDB for database 'BPO' finished without errors on 2009-12-15 11:30:28.320 (local time). This is an informational message only; no user action is required.
3. When a database is in SUSPECT mode you will not be able to get connected to the database. Hence you need to bring the database first in EMERGENCY mode to repair the database. Execute the below mentioned TSQL code to bring the database in EMERGENCY mode.
USE master GO ALTER DATABASE BPO SET EMERGENCY GO
Once the database is in EMERGENCY mode you will be able to query the database.
4. Execute the DBCC CHECKDB command which will check the logical and physical integrity of all the objects within the specified database.
DBCC CHECKDB (BPO) GO
DBCC CHECKDB will take time depending upon the size of the database. Its always recommended to run DBCC CHECKDB as part of your regular maintenance schedule for all the SQL Server Databases.
5. Next step will be to bring the user database in SINGLE_USER mode by executing the below mentioned TSQL code.
ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
6. Once the database is in SINGLE_USER mode execute the below TSQL code to repair the database.
DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS) GO
7. Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.
ALTER DATABASE BPO SET MULTI_USER GO
This articles mentions the steps a DBA need to follow to recover database which is marked SUSPECT. As mentioned above, executing DBCC CHECKDB… REPAIR_ALLOW_DATA_LOSS DBCC Command will result in Data Loss. Therefore, if you have database backups which will allow “Point In Time” and have enough space on the server then it is the recommended approach.
SQL Server Disaster Recovery Tips
- Improved Startup Parameters in SQL Server 2012
- SQL Server Database Engine Service Startup Options
- How to Configure TempDB on Local Disk in SQL Server 2012/2014 Failover Cluster to Improve Performance
- Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide
- How to Fix “BACKUP detected corruption in the database log” Error in SQL Server
- How to Repair Database in Suspect Mode in SQL Server
- How to Start SQL Server without TempDB Database
- Why to Configure Database Instant File Initialization in SQL Server? Advantage
- Different Ways to Enable Dedicated Administrator Connection in SQL Server
Top Trending SQL Server DBA and Developer Articles and Tips
- How to Repair Suspect Database in SQL Server
- SQL Delete Duplicate Rows from a SQL Table in SQL Server
- How to Enable Dedicated Administrator Connection in SQL Server
- How to Configuring Database Instant File Initialization Feature of SQL Server
- How to Backup and Restore Resource Database in SQL Server
- How to Start SQL Server in Single User Mode?
- How to Start SQL Server with Minimal Configuration
- How to Detect Virtual Log Files in SQL Server Transaction Log File
- How to Identify the Location of Resource Database in SQL Server
- How to Disable an Index in SQL Server
- How to Monitor Transaction Log File Usage in SQL Server
- How to Connect to SQL Server When all System Administrators are Locked Out