“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.
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,
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,
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.
In order to recover SQL Server database suspect mode to normal mode, follow the below listed steps in sequence,
EXEC sp_resetstatus ‘database_name’; ALTER DATABASE database_name SET EMERGENCY
DBCC CHECKDB (‘database_name’)
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE database_name SET MULTI_USER
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.
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.