How to fix Recovery Pending State in SQL Server Database?

Summary:  This post will outline reasons why a database (db) is marked in recovery pending state. Also, it will describe methods to fix ‘SQL server database in recovery pending state’ problem. You can resolve the problem by executing queries in SQL Server Management Studio (SSMS), or by using Stellar Repair for MS SQL software. 

SQL Server Database States

A SQL database is considered to be damaged if one or more of its core files are in an inconsistent state. Depending on how severe the damage is, the db is marked with different states. Some of these states are:

  • Online – If one of the data files is damaged when executing a query or some other operation, the database will remain online and accessible.
  • Suspect – If a database cannot be recovered during startup of SQL Server, the database is marked as Suspect.
  • Recovery Pending – If the SQL Server knows that database recovery needs to be run but something is preventing it from starting, the Server marks the db in ‘Recovery Pending’ state. This is different from the SUSPECT state because it cannot be said that recovery is going to fail – it just hasn’t started yet.
Recovery Pending State in SQL Server
Figure 1- Database marked in ‘Recovery Pending’ state

Let’s first understand how you can check the current state of the database.  

How to Check Database State?

 To check the current state of an SQL db, run the following query:

SELECT name, state_desc from sys.databases

GO

Executing the query will give you an output that looks something like:

Different Database States
Figure 2- Database States

Reasons behind Recovery Pending State in SQL Server

Some of the reasons causing such an issue are:

  • The database didn’t shut down properly and there is at least one uncommitted transaction active during the shutdown, resulting in deletion of the active transaction log file.
  • User tried moving the log files to a new drive to overcome server performance issues, but ended up corrupting the log files in the process.
  • Database Recovery cannot be initiated due to insufficient memory space or disk storage.

Methods to Fix Recovery Pending in SQL Server Database Issue

NOTE: Before initiating any of the following repair procedures, make sure to take db backup, so as to have a fail-safe copy in case anything goes wrong.

Method 1 – Manual Ways to Resolve the Database in Pending State

Following are the two manual ways that helps bring SQL database in recovery mode to normal mode:

1. Mark Database in Emergency Mode and Initiate Forceful Repair

Database EMERGENCY mode marks the database as READ_ONLY, disables logging, and grants access only to system administrators. Essentially, setting the db in this mode can bring the inaccessible database online. 

NOTE: Usually a database comes out of EMERGENCY mode automatically. If you are having issues restoring the db from this mode, read this Recover SQL Database from Emergency Mode to Normal Mode.

Once you have opened the db in EMERGENCY mode, try repairing the database using the DBCC CHECKDB command with the ‘REPAIR_ALLOW_DATA_LOSS’ option. To do so, open SSMS and execute the following set of queries:

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

2. Mark Database in Emergency Mode, Detach the Main Database and Re-attach It

This solution also requires to mark db in EMERGENCY mode. Once done, take the database offline (detach) and then bring it online (re-attach). To do so, execute the following set of queries in SSMS:

ALTER DATABASE [DBName] SET EMERGENCY;
ALTER DATABASE [DBName] set multi_user
EXEC sp_detach_db ‘[DBName]’
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’

The above set of queries help make the server get rid of the corrupt log and build a new one automatically.

Method 2 – Use Stellar Repair for MS SQL Software

If the above methods cannot help resolve the db in recovery pending state issue, use Stellar Repair for MS SQL software. The software can help you repair corrupt database files and bring the database back online from recovery pending state quickly and smoothly.

Steps to Use Stellar Repair for MS SQL Software

Step 1: Download, install and Run Stellar Repair for MS SQL software.

Step 2: From Select Database window, click Browse or Search to select the database file you want to repair. 

Select corrupt sql database file
Figure 3 – Select Database File

Step 3: Click Repair to start the repair process.

Step 4: The software shows preview of all the recoverable db components.

Preview sql repaired file
Figure 4 – Preview Windows

Step 6: To save the repaired file and its components, click Save on File menu. 

Step 7: From the Save Database window, do the following:

  • Choose MDF under Save As.
  • Select New Database or Live Database under Saving Option.
  • Enter the required details in Connect to Server section.
  • Click Browse to choose the destination for saving the repaired file.
SQL database file saving options
Figure 5 – File Saving Options

Step 8: Hit Save.

Step 9: When the ‘Save Complete’ message box appears, click OK.

Saving process complete
Figure 6 – Save Complete Window

The repaired file will get saved in the selected location.

NOTE: For detailed information about using Stellar Repair for MS SQL software, refer to this user guide.

Key Features of Stellar Repair for MS SQL

  • Repairs corrupt and inaccessible MS SQL Database (.mdf and .ndf) files.
  • Recovers all database components like tables, keys, indexes, triggers, rules and stored procedures, etc.
  • Recovers deleted records.
  • Supports MS SQL 2019, 2017, 2016, 2014, 2012, 2008, 2008 R2 and all lower versions.
  • The software is trusted by Microsoft MVPs.
Recovery Pending State in SQL Server

Conclusion

This post outlined reasons behind ‘db in recovery pending state issue like database is not cleanly shut down, database files (.mdf or .ndf) turned corrupt, insufficient memory or disk space. It also described methods to fix Recovery Pending State in SQL Server Database.

You can fix the database by setting it to emergency state, and initiating the repair process or de-attaching and reattaching the db. But, such solutions have their own downsides:

  • It can only resolve minor database issues.
  • It does not ensure complete recovery of the database.
  • It involves data loss risk, particularly, running ‘REPAIR_ALLOW_DATA_LOSS’ command can delete some data.

You can overcome these limitations by using Stellar Repair for MS SQL software. The software helps SQL users and database administrators (DBAs) to fix severe database corruption errors. Also, it ensures complete recovery of db components, while preserving data integrity.

Comments(27)
  1. Kevin James June 13, 2020
  2. Nick Smith March 23, 2019
    • Eric Simson March 24, 2019
  3. Calvin January 31, 2019
  4. Hans January 20, 2019
  5. Waleed Elbahr December 4, 2018
    • Eric Simson December 5, 2018
  6. Alfredo P. September 23, 2018
    • Eric Simson September 24, 2018
  7. Leo Paul September 22, 2018
  8. Jagdeep Mankotia July 11, 2018
    • Eric Simson July 12, 2018
  9. Noel May 9, 2018
    • Priyanka Chauhan May 9, 2018
  10. Grant April 4, 2018
    • Priyanka Chauhan April 4, 2018
  11. Ruhel Malik March 27, 2018
    • Eric Simson March 27, 2018
  12. raveshan February 28, 2018
  13. Jackson Robert February 22, 2018
    • Eric Simson February 23, 2018
  14. Mike Prestia December 28, 2017
    • Eric Simson December 29, 2017
  15. Devin Mason November 28, 2017
    • Eric Simson November 29, 2017
  16. Neil Richards November 23, 2017
    • Eric Simson November 28, 2017

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.