How to Change Suspect Mode to Normal Mode in SQL Server Database

If you are reading this, you are desperately looking for solutions to change suspect mode to normal mode. It also means that your database has gone into the suspect mode. In that case, you have already experienced one of the most irritating situations possible for a DBA.

Worry no more, as you will soon get out of this situation. In this post, we have compiled some of the prominent solutions to the query, “how to change suspect mode to normal mode in SQL”. We will also tell you in detail in which situations a database can turn into suspect mode and what can be done to get back the database into normal mode.

How to Change Suspect Mode to Normal Mode – Possible Scenarios

In this section, let us check some of the common scenarios responsible for suspect mode of SQL database.

Scenario 1: “Some inexperienced employees used SQL database yesterday and now the database is in suspect mode. I guess they may have misplaced some data. How to bring back a Database Online from Suspect mode?”

What happens in this case is some data or log files get deleted (or misplaced) from the database when the Server is in offline mode. As a result, the database fails to start and goes into the suspect mode.

Solution: If the suspect mode situation occurs due to this reason, the error message of SQL Error Log will contain the name and directory of the missing file. Place back the file and then run this command to put the database online in normal mode without any data loss.

RESTORE DATABASE WITH RECOVERY

Scenario 2: “Some applications have locked our database files and now the database cannot come online. How can we remove the suspect mode and bring in online in the normal mode?”

In this particular situation, SQL Server fails to put an exclusive lock on the log file or data when it tries to come online. It also fails to access a lock placed on data/ log file by some other tools like anti-virus applications (happens if SQL Server is shared with these tools).

Solution: If the above reason has put the database in suspect mode, you have to kill the file handler responsible for placing lock on the file. Take the help of Process Explorer to do this and the involvement of System Admins is recommended to perform this particular step. Later, run this command to put SQL database into normal mode again.

RESTORE DATABASE WITH RECOVERY

Scenario 3: “Due to power surge, SQL Server got shut down suddenly while it was in the middle of a transaction. Now the database is offline with suspect mode because of the corrupt transaction. Tell me how to bring the database back into normal mode.”

Here, a corrupt transaction that took place in the database has put the database into the suspect mode. This is often considered as the worst possible scenario as it contains high chance of data loss if there is no good backup. This is also the main reason for suspect mode in OLTP databases. If SQL Server is been closed abruptly or restarted while in the middle of a transaction and then it failed to complete the transaction (whether commit or rollback), this scenario occurs.

Solution: If you have a good backup of your database available, you can restore the database up to a suitable point. If you do not have this option available, you need to run the DBCC CHECKDB Repair_Allow_Data_Loss command. These are the steps you have to perform on SQL Server Management Studio.

Remember: Following the below steps can result in data loss from your database. So, we recommend you to try this only after you have tried every other solution.

  • Use SQL Server Management Studio to connect to SQL database and find out which database has gone into suspect mode. To do this, run the command:
  • USE master
    GO
    SELECT NAME, STATE_DESC FROM SYS.DATABASES
    WHERE STATE_DESC='SUSPECT'
    GO
  • Expand Management Node > SQL Server Error Logs and open the SQL error log. Check if the database is marked suspect in the error log.
  • Now, put the suspect database into Emergency mode, since it is not feasible to connect to a database that is in suspect mode. Run this command:
  • USE master
    GO
    ALTER DATABASE DBO SET EMERGENCY
    GO

    Note: Before you enable Emergency Mode, remember that it cannot be rolled back. So, we suggest you to take data backup if possible before applying this command.

  • Now the database has gone into read-only state and only system admin privileges can access the database. You can run this command to verify the physical and logical integrity of all database objects:
  • DBCC CHECKDB (DBO)
    GO
  • This command will enable single user mode for the database. It will also check if rollback is available:
  • ALTER DATABASE DBO SET SINGLE_USER WITH ROLLBACK IMMEDIATELY
    GO
  • It is time to repair the database and you need to use the below DBCC CHECKDB command:
  • DBCC CHECKDB (DBO, REPAIR_ALLOW_DATA_LOSS)
    GO

    Warning: Once you have run this command, there is no rollback available. Any lost data will be lost forever.

  • Put the database into MULTI_USER mode so that anyone can use the database. Run this command:
    ALTER DATABASE DBO SET MULTI_USER
    GO

You should have your database back in online mode by now. You have to check how much data you have lost.

Here are some of the other reasons that can put SQL database into suspect mode:

  • SQL database MDF file corruption
  • Problems during rollback or transaction completion
  • Errors due to limited space on disc or system drive.
  • Transaction log corruption
  • When the Server cannot access the drive where the log files are stored
  • Corruption of hard disk

Change Suspect Mode to Normal Mode Without Any Data Loss

Manual approaches that users implement to turn the suspect database into normal mode involves loss of valuable data. Most of the SQL users cannot afford to lose their data and hence they look for some better solution. SQL Database Repair Software is such an application that can fix all the scenarios responsible for suspect mode and put in back to normal mode without losing any data. To run this application, you have to perform these steps:

  1. Add the database file with suspect mode in the software.
  2. Apply Advanced scan mode to fix any discrepancy.
  3. The tool will scan the database and repair it.
  4. The complete recovered database will be available for preview.Click Export the save the required database components in any location.

Conclusion

Since the suspect mode is a common issue faced by SQL users, these users keep asking in forums, “how to change suspect mode to normal mode in SQL?” Their search should end here as we have depicted some verified methods to put SQL database into normal mode. Use any of the solutions mentioned here to get rid of the suspect mode SQL database.

Advertisements

About Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management etc.
This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s