SQL Database in Recovery Pending State

Thank to: https://www.stellarinfo.com/

How to fix Recovery Pending State in SQL Server Database?

SQL database states

An SQL database is considered to be damaged if one or more of its core files are in the inconsistent state. Depending upon how severe the damage is, the database is marked with different states. Check the few states below:

• Online – If one of the data files has been damaged during a query or some other operation, the database will remain online and accessible.
• Suspect – If the transaction log is damaged and it prevents recovery or a transaction rollback from completing or causes it to fail.
• Recovery Pending – If the SQL Server knows that database recovery needs to be run but something is preventing it from starting. This is different from the SUSPECT state because it can’t be said that recovery is going to fail – it just hasn’t started yet.

The state an SQL database is in can be checked by running the following query:

SELECT name, state_desc from sys.databases

GO

The output will be:

Primary Reasons behind “Recovery Pending State”

Decoding where an error originates from and what is causing it as crucial as finding a sure-shot solution to it. When database recovery is required but cannot be initiated, an SQL database is marked in Recovery Pending state. This kind of situation arises primarily when:

• The database isn’t cleanly shut down, that is, there is at least one uncommitted transaction active at the time the database is shut down and the log file for it has been deleted
• User has tried to move the log files to a new drive to overcome server performance issues but in the process, ended up corrupting the log files
• Database Recovery cannot be initiated due to insufficient memory space or disk storage

Manual way to fix “SQL server recovery pending” state

There are 2 manual ways to start database recovery with an SQL database that has been marked in recovery pending state:

Solution 1: Mark database in Emergency mode and initiate forceful Repair

1. 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. EMERGENCY mode marks the database as READ_ONLY, disables logging, and grants access only to system administrators.
3. This should resolve any corruption and bring the database online. The database will come out of EMERGENCY mode automatically.

Solution 2: Mark database in Emergency mode, detach the main database and re-attach it

1. Execute the following set of queries:

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]’

2. These commands will cause the server to get rid of the corrupt log and build a new one automatically.

Note: Before initiating any of these repair procedures, you should ensure that you have proper backups of the database in question. This is to have a fail-safe copy in case anything goes wrong. Also, remember that these are highly technical procedures and you shouldn’t be performing them if you are unsure or do not have proper technical expertise.