Home » SQL » Steps to Drop or Suspect Database in SQL Server Database

Steps to Drop or Suspect Database in SQL Server Database

👤 Naveen Sharma | Published: May 8th, 2023 | SQL | 5 Minutes Reading

“Need help urgently! Yesterday I have noticed that one of my databases is showing the status of Suspect mode. Now, I need to drop it and recreate the database. While trying to do the same, it shows a prompt message that the database is in use and moreover I cannot find any session using the database. When I execute sp_who and I could see that there is no related session for it. Please help me know the process to drop or delete a suspect database.”

There has been a rigorous demand among the users seen in various forums when the database goes in suspect mode and one needs to drop or delete a suspect database. Hence, we have majorly considered this request and come up with this post where we will elaborate the reasons for suspect database with its reliable solution and let you know how to recover suspect database.

Possible Reasons for Suspect Database

When the SQL Server database goes into suspect mode, the user won’t be able to access nor work on the database. One of the reason is when the primary file is damaged and the database cannot be restored back during the startup of the SQL Server database. Well, there are numerous reasons for the same and some of them are disclosed below,

  • When the hard drive becomes inaccessible due to any corruption in the hard drive partition can lead the database to go under suspect mode
  • Any unknown virus or malware attack can delete the crucial files from the server
  • When there occurs any damage to the transaction log file or MDF file due to server crash during the middle of the transaction, then the database becomes suspect
    If the disk space is limited or there is no more space to accommodate any more files, then there are chances the database can get suspect
    When the SQL Server fails to complete the rollback or roll forward transaction
    Another reason can be due to restricted space for FAT32 file systems that will ultimately cause the database to go under suspect mode

How to Drop a Suspect Database?

Whenever the user tries to open the database, it goes in recovery mode after which it fails to open the database. Therefore to drop a suspect database manually, following are the detailed steps to process the same,

  • Initially, stop using the SQL Server service
  • In order to backup the files, ensure to take a copy of MDF/NDF and LDF files
  • After that, rename the database files i.e., MDF or LDF or both the files
  • Once it is done, now start the SQL Server service

As the files are no more available now, hence the database startup would fail. Due to which, it will now go under Recovery Pending state as this will allow to drop the database.

How to Recover SQL Server Database in Suspect Mode

In order to recover SQL Server database suspect mode to normal mode, follow the below listed steps in sequence,

  • First of all, launch SQL Server Management Studio on your system and connect to the database
  • Now, hit the new query option and turn off the suspect flag and set it to Emergency mode to gain access to the SQL Server database
EXEC sp_resetstatus ‘database_name’;
 ALTER DATABASE database_name SET EMERGENCY
  • After which, run the consistency check on the master database with the following commands to recover suspect database
DBCC CHECKDB (‘database_name’)
  • Now, switch the database to a Single User mode and roll back any previous transaction that has not completed successfully
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  • Make sure to take entire backup of the database. If any errors are encountered in the DBCC CHECKDB command, then repair the database using the below command
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)
  • At last, change the Single User mode to Multi User mode. Then, refresh and check the connectivity of the database
ALTER DATABASE database_name SET MULTI_USER

What If the Above Method Fails to Recover Suspect Database?

The worst situation is when you know that the aforesaid solution didn’t help to recover suspect database when the file is highly corrupted or damaged. Under such circumstance, it is advised to opt some reliable third-party software like SQL MDF Recovery Tool. It is a versatile and feature-rich software which offers remarkable features to the end users. It is efficient enough to repair and recover both primary and secondary database file. It provides dual mode options to scan the severely corrupted files using Quick and Advance scan mode. Additionally, this tool is compatible with the latest SQL Server 2017 and the previous versions.

Time to Conclude

Database which stores the crucial data goes under suspect mode due to various reasons, the user moreover gets annoyed due to lack of data accessibility. Hence, we have described the manual workarounds to drop a suspect database and know how to recover database from Suspect mode. If in case the database file are severely corrupted, it is wise to opt the best professional tool mentioned in this blog.