Top Reasons for SQL Server in Recovery Pending State and How to Prevent Them

The recovery pending state is a common error that occurs when performing database recovery in the SQL Server Database. It makes the database unable to initiate a recovery process or to commit an existing recovery process. The reasons may be internal or external. For instance, it could be because of adding more disk space, a power outage, a system crash, database file rebuilding, or several other reasons. Here is more on this topic and its resolution. If you face hassles in manual fixing of the issue, you may also opt for professional SQL recovery software, such as Stellar Repair for MS SQL, which we will talk about later.

SQL Server in Recovery Pending State: Top Reasons

Many factors can lead to this state, which can become clear when you go through the SQL Server error logs and the Windows Event Viewer. On checking the log, you may find any of these reasons

1. Login failed error

When examining the log, you may find this message: 

Login failed for user ‘XYZ’
Error: 18456, Severity: 14, State: 38

Here, state 38 refers to a valid login, but it still cites the inability of the SQL Server to open the specified database. Apart from the Recovery Pending state, other reasons behind this error could be 

• An offline database, 
• Inaccessible database file, or 
• Inability to log in to the default database.

2. SQL Server cannot open the database

This error happens when SQL Server is unable to access the requisite database files. It could happen due to several reasons, such as:

1. Missing or inaccessible database file because of: 

• Deleted MDF/LDF file
• Change in file path or drive letter
• Disconnected external storage, 
• No availability of the network share 

2. Insufficient file system permissions, due to:

• Recently changed service account or folder permission 
• Antivirus installation  
• Lost NTFS permissions

3. Disk Space Shortage, which happens due to:

• Full data drive, TempDB drive, or log drive
• Failed auto-growth due to the unavailability of free space

4. Other Reasons

Other reasons behind the ‘SQL Server cannot open the database’ issue, which eventually can lead to ‘Recovery Pending’ State, may include:

• Corrupted MDF/LDF database files due to an abrupt system shutdown
• Missing or damaged log files
• Bad sectors, disk failure, RAID controller issues
• Database already in SUSPECT mode

3. Disabled Mirroring endpoint

You may encounter the following message in the error log: 

“The Database Mirroring endpoint is in a disabled or stopped state.”

This issue occurs due to issues in the mirroring endpoints of the SQL Server database. It may not lead to the recovery pending state directly, but it does indicate: 

• Incomplete endpoint mirroring configuration
• A server restart without complete recovery

Error Codes that indicate Recovery Pending State of the SQL Server Database 

Here are the key error codes you may encounter while checking the error log for the reasons behind the Recovery Pending state:

• Error 945: Indicates the inability of the SQL Server instance to open the database due to insufficient memory space or inaccessible database files.
  
• Error 829: Shows a particular database page marked as RestorePending, due to I/O errors, disk corruption, or an incomplete page restore operation done manually.
  
• Error 5105: It shows, “A file activation error occurred” message, and happens due to several issues in accessing the MDF or LDF database files. These may include file path issues or system-level errors, which do not necessarily relate directly to the SQL Server database.
  
• Error 9001: Indicates a damaged or unavailable transaction log for a specific database. It is a severe issue. Checking the operating system error logs would be advisable to delve deeper into the problem, fix it, and go for a database restart.
  
• Error 921: This severe issue indicates the attempt to access a database in its recovery phase between server startup. It may also point to accessing a database, already marked as Suspect because of a previous issue.

Fixing the Recovery Pending State in SQL Server

Knowledge about the reason behind an error makes it easier to move on with the resolution steps. But before moving ahead with fixing steps, you should check the current state of your database:

1. Check the current database state

To check the existing database system, do the following:

1. Open Microsoft SQL Server Management Studio
2. Click New Query from the toolbar, below the main menu
3. In the resulting SQL Query window, type the following T-SQL command

SELECT name, state_desc FROM sys.databases

4. It will list all the databases in your SQL Server, followed by their existing status, whether online, recovery pending, or something else.
5. If it comes out as recovery pending, proceed with the recovery steps

  Here is the step-by-step method to fix this issue.

2. Set the state of the database to ONLINE using DBCC CHECKDB

This method can fix the issue caused due to insufficient disk space. The name of the database used here is TestDB. When executing it at your end, replace it with your database name. Here is the command:

ALTER DATABASE TestDB SET ONLINE

DBCC CHECKDB (TestDB) WITH NO_INFOMSGS

If the command fetches success, your database should have acquired the ONLINE state. It will not require you to repair the database.

3. Rebuild the log files through the rebuild query

 If setting the database ONLINE does not fix the issue, you may opt to rebuild log files in Emergency mode.

1. Set the database to EMERGENCY mode to allow its access with limited functionality.

              ALTER DATABASE TestDB SET EMERGENCY;

2. Rebuild the transaction log file by using the T-SQL command below:

ALTER DATABASE [Database_Name] REBUILD LOG ON (NAME = logical_file_name, FILENAME = ‘path’);

For example: ALTER DATABASE [TestDB] REBUILD LOG ON (NAME = TestDB_log, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA’)

3. After rebuilding the log file of the database, set it to ONLINE.

ALTER DATABASE TestDB SET ONLINE;

4. Verify the database status by using the following query:

SELECT database_id, name, state_desc FROM sys.databases WHERE name=’TestDB’ 

As you can infer from the screenshot above, the database is not set to ONLINE. Here, it is critical to know that rebuilding the database leads to the loss of uncommitted transactions. To avoid such issues, always prefer restoring database from a recent backup.

4. Rebuild the transaction log file using the ‘database detach and attach’ method

As an alternative, you may also rebuild the transaction log by database detach and attach method. Just keep in mind that you cannot detach a published or a replicated database. The same holds for a database, which is in a mirroring session or in suspect mode. Here are the steps:

1. Set the database to EMERGENCY mode: ALTER DATABASE [TestDB] SET EMERGENCY;
2. Change the database mode to MULTI_USER : ALTER DATABASE [TestDB] SET MULTI_USER
3. Detach the database: 

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N’TestDB’

GO

4. Next, reattach the database: 

USE [master]

GO

CREATE DATABASE [TestDB] ON 

( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\TestDB.mdf’ ),

( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\TestDB_log.ldf’ )

FOR ATTACH

GO

5. This will reattach both the MDF file and the transaction log file of the database.

5. Repair the database

If the rebuild method does not resolve the recovery pending state, you can opt for the database repair method by using DBCC CHECKDB. Here are the steps:

1. Scan the database for any inconsistencies: DBCC CHECKDB(TestDB) with NO_INFOMSGS;
   
2. If the command shows any consistency errors, start the repair by setting it to EMERGENCY mode:
   
   ALTER DATABASE [TestDB] SET EMERGENCY;
   
3. Set the database to SINGLE_USER mode: ALTER DATABASE [TestDB] SET SINGLE_USER;
   
4. Run the DBCC CHECKDB command using the ‘REPAIR_ALLOW_DATA_LOSS’: 

DBCC CHECKDB ([TestDB], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

5. This will repair the database, but with cause loss of data during the procedure
   
6. Reset the database mode to MULTI_USER:   ALTER DATABASE [TestDB] SET MULTI_USER

These manual methods can fix the recovery pending issue, but can cause data loss and do not guarantee complete recovery either, possibly due to non-clustered indexes. Moreover, you have to ensure that no one else is using or modifying the database while the recovery is in process. To avoid these instances and to ensure hassle-free database recovery, it is wise to confide in professional SQL recovery software.

Fixing Recovery Pending State by Using Stellar Repair for MS SQL

This professional SQL recovery software repairs damaged MDF and LDF files and rebuilds all SQL Server database objects. During the process, it ensures complete data integrity. Admins prefer this software due to its multiple features: 

• Repairs MS SQL tables, indexes, keys, etc.
• Resolves SQL database corruption resulting from several errors
• Repairs the corrupt backup file and extracts the database
• Fixes all the DBCC CHECKDB Consistency Errors
• Repairs SQL database file header corruption
• Fixes corrupted SQL Server Database index
• Retrieves deleted records from the SQL database
• Allows the option to preview Repairable MS SQL Database Objects

Using the software is easy and self-explanatory. For details, you can refer to the official user manual of Stellar Repair for MS SQL. The organization also offers a free trial version of the application to scan and preview recoverable objects. To buy the professional SQL recovery software, you can browse through the official landing page of Stellar Info. Just select from the Corporate, Technician, and Corporate editions, subject to your requirement.

Conclusion

You may feel the need to recover the database due to various reasons, as mentioned in the SQL Server error logs and the Windows Event Viewer. Prominent of these errors include login failed, SQL Server cannot open the database, Disabled Mirroring endpoint, and so on. These errors happen due to various factors, such as power outages, system crashes, insufficient disk space, installation of a new antivirus, deleted log files, and more. Just to help you out, some common ERROR CODEs indicating a recovery pending state are Error 945, 829, 5105, 9001, and 921.

Admins use systematic recovery methods to rescue the database from this situation. The ideal start would be to check the existing state of the database and decide the right course of action. Before trying any method, it is crucial to have a recent backup of the database. At times, the solution is as easy as setting the database status to Online. The other times, you may have to rebuild the log files. If this does not work out, there is always the option to use the REPAIR_ALLOW_DATA_LOSS option with DBCC CHECKDB. However, it will lead to considerable data loss, as the command will delete all the uncommitted transactions to repair the database. 

To avoid indulging in such situations, you can opt for professional SQL recovery software, such as Stellar Repair for MS SQL. It is easy to use, quickly repairs the corrupt database, and recovers the missing data, bringing the functioning of your organization back on track. You can order Stellar Repair for MS SQL from the Stellar Info website. Besides, the company also offers you to download its free trial version with restricted functionality.