SQL Server Database in Suspect Mode

SQL server issue and solution 

Error Summary:  

Suspect is a state where database becomes inaccessible due to different reasons. 

These are the few reasons why the database will go to Suspect mode. Most of the common reasons are mentioned below: 

REASONS: 

1. If the Data and Log Files are missing or corrupted or damaged.
2. While restoring the database if the SQL Server was stopped unexpectedly.
3. 3. If more than 1000 pages of data and Log files are
4. If there is disk I/O error from Data or Log File 
5. If Database Server was shut down improperly.
6. If SQL cannot complete a Rollback or Roll forward operation.

Errors: 

17204- If the Data file was corrupted. 

17207- If the Log File was corrupted. 

 

RESOLUTION: 

Scenario 1: If the Data File was Damaged (17204) 

Step 1. Check if there is Error (17204) in the Error Log. 

Step 2. Take the Transaction Log Backup. 

Step 3. Restore last Full Backup  

Step 4. Restore Transaction Log Backup. 

Step 5. Database Comes Online. 

 

Scenario 2: If the Transaction Log File was Damaged (17207) 

• Take any user defined Database for example: Mydb 
• Check the current location of the Files. 

             Sp_helpdb MyDB 

• Stop the server or Take the Database Offline. 

• Move the Transaction Log File into the different folder.  
• Start the Server–>DB goes into Suspect Mode. 

        Select Databasepropertyex(‘mydb’,’status’) 

• Check the Error log for possible reason. 

Steps to Recover: 

Step 1: Make the Database into single user mode. 

             Alter Database Mydb set Single_User  

Step 2: Set the Database into Emergency mode. 

             Alter Database Mydb set Emergency.  

Step 3: Run the Checkdb with required Repair level. 

             DBCC CheckDB(‘Mydb’, repair_allow_data_loss) 

Step 4: Set the Database in Muti User mode. 

             Alter Database Mydb set Multi_User  

            Database Comes Online. 

 

Scenario 3: If the File is Full  

Step 1: Execute Sp_resetstatus 

             Sp_resetstatus Database_name  

Step 2: Use ALTER DATABASE to add the data file or log file to the database. 

Step 3: Stop and restart the SQL Server 

With the extra space provided by the new data file or Log file, the SQL server will be able to complete recovery of the Database.  

 

Scenario 4: If the page is Corrupted. 

Restore from Recent Backup File by using the below command. 

Restore Database dbname page= (File id: page id) from disk=’backup file path’