MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

How to fix SQL Database in Recovery Mode in SQL Server 2014 & 2016

Published on 15 December 18
336
0
0

Being a database administrator, you might have faced Database in Recovery mode error. The primary reason behind the occurrence of Recovery pending state error in SQL Server 2016, 2014 and all the below versions of SQL Database is storage issue in the database partition. Here, in this article, we are going to explain how easily one can fix SQL Database in Recovery mode using two easy manual methods.

What are the reasons behind SQL Database in Recovery mode?

Before going to the solution part, let know the occurrence of recovery pending state in SQL Database.

  • Hardware or software issue
  • Corrupt MDF files
  • Improper termination of SQL Database
  • Storage issue due to full database partition
  • Insufficient disk space

Manual Methods to fix SQL Database in Recovery mode or Suspect mode

Though, manual methods are quite challenging and tricky in order to keep the database away from recovery pending or suspect mode. Hence, it is advised to keep a backup copy of your original SQL database files.

Method 1: Set database in Emergency mode and begin the forceful repair

ALTER DATABASE (Database Name) SET EMERGENCY;
GO
ALTER DATABASE (Database Name) set single_user
GO
DBCC CHECKDB ([Database Name], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE (Database Name) set multi_user
GO

Once the database is marked as READ_ONLY by Emergency mode, just disable the logging and grant access to the administrators.

Once the above-steps gets done, the database will come out of Emergency mode immediately.

Method 2: Detach and re-attach the database soon after marking the database in Emergency mode

ALTER DATABASE (Database Name) SET EMERGENCY;
ALTER DATABASE (Database Name) set multi_user
EXEC sp_detach_ db ‘(Database Name)’
EXEC sp_attach_single_file_db @Database Name = ‘(Database Name)’, @physname = N’(mdf path)’

Once these steps are performed, corrupt log files will be removed and build a new one instantly.

Conclusion

As above said, I have explained two manual methods to fix SQL Database in recovery mode. However, if you manual methods fail to fix Recovery Pending State in SQL Server, you can take the help of any third-party SysInfo SQL Database Recovery tool. I hope this blog will help you out to resolve your recovery pending issue.

Being a database administrator, you might have faced Database in Recovery mode error. The primary reason behind the occurrence of Recovery pending state error in SQL Server 2016, 2014 and all the below versions of SQL Database is storage issue in the database partition. Here, in this article, we are going to explain how easily one can fix SQL Database in Recovery mode using two easy manual methods.

What are the reasons behind SQL Database in Recovery mode?

Before going to the solution part, let know the occurrence of recovery pending state in SQL Database.

  • Hardware or software issue
  • Corrupt MDF files
  • Improper termination of SQL Database
  • Storage issue due to full database partition
  • Insufficient disk space
Manual Methods to fix SQL Database in Recovery mode or Suspect mode

Though, manual methods are quite challenging and tricky in order to keep the database away from recovery pending or suspect mode. Hence, it is advised to keep a backup copy of your original SQL database files.

Method 1: Set database in Emergency mode and begin the forceful repair

ALTER DATABASE (Database Name) SET EMERGENCY;
GO
ALTER DATABASE (Database Name) set single_user
GO
DBCC CHECKDB ([Database Name], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE (Database Name) set multi_user
GO

Once the database is marked as READ_ONLY by Emergency mode, just disable the logging and grant access to the administrators.

Once the above-steps gets done, the database will come out of Emergency mode immediately.

Method 2: Detach and re-attach the database soon after marking the database in Emergency mode

ALTER DATABASE (Database Name) SET EMERGENCY;
ALTER DATABASE (Database Name) set multi_user
EXEC sp_detach_ db ‘(Database Name)’
EXEC sp_attach_single_file_db @Database Name = ‘(Database Name)’, @physname = N’(mdf path)’

Once these steps are performed, corrupt log files will be removed and build a new one instantly.

Conclusion

As above said, I have explained two manual methods to fix SQL Database in recovery mode. However, if you manual methods fail to fix Recovery Pending State in SQL Server, you can take the help of any third-party SysInfo SQL Database Recovery tool. I hope this blog will help you out to resolve your recovery pending issue.

This blog is listed under Data & Information Management Community

Related Posts:
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
 
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url