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.

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

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.