未加星标

Steps to Drop or Suspect Database in SQL Server Database

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏
“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.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Steps to Drop or Suspect Database in SQL Server Database
本站链接:https://www.codesec.net/view/627745.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(274)