SQL SERVER – How to Fix a Database in Recovery Pending State

A SQL database will be marked with different states if one or more of its core files are in a inconsistent state. The type of state will depend on how serious the damage is. Here are some of the states:

• Online – occurs when one of the data files is damaged during an execution of a query or some other operation.
• Suspect – occurs when a database cannot be recovered during startup
• Recovery Pending – occurs when SQL Server knows that recovery of the database is to be done, but something is obstructing before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, because it has not started yet

Here a script to check the current state of all your databases in a SQL instance.

SELECT name, state_desc from sys.databases
GO

There are several reasons why a database is marked as ‘Recovery Pending‘

• The database was not cleanly shutdown. There could be one or more transactions active at that time, resulting in the deletion of active transaction log file.
• To overcome server performance issues, A user could have tried moving the log files to a new drive, but in the process, corrupted the files instead.
• Insufficient memory or disk space that prevented database recovery from getting started.

Let’s Fix It

ALTER DATABASE TestDB SET EMERGENCY;
GO

ALTER DATABASE TestDB set single_user
GO

DBCC CHECKDB (OptimalTestDb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO

ALTER DATABASE OptimalTestDb set multi_user
GO

Cheers!