Fix Recovery Pending State in SQL Server database

Summary: This post will offer you reasons for recovery of pending state in SQL Server. Also, it will provide you a quick method to fix "SQL Server database in recovery pending state" problem.

States of SQL Server Database

If one or more core files of SQL are in inconsistent state then SQL database is considered to be damaged. Here are the states of damage.

Online –While executing a query, if one of the data files is damaged, the database will remain online.

Suspect –If the database can't be recovered during backup of SQL server, the database is marked as suspect.

Recovery Pending –If the SQL Server requires to run database recovery, but something is preventing from starting, the server considers the db in 'Recovery pending' state.

What are the reasons for Recovery Pending State in SQL database

  • Corruption in log files
  • Damage or corruption in MDF files
  • Hardware Failure
  • Incomplete tasks or shutting down when some tasks were pending
  • Lack of memory space or database partition is full
  • Starting the server with time gaps

Fix Recovery Pending in SQL Server Database Problem

Here is one smart way to fix recovery pending in SQL database.

1. Mark Database in Emergency Mode and Start Forceful Repair

Database EMERGENCY mode considers the database as READ ONLY, disable logging, and allow access only to system admin. By setting database in this mode will bring inaccessible database online.

Once you open database in EMERGENCY mode, try repairing the database using DBCC CHECKDB command with the REPAIR ALLOW DATA LOSS option. To do this, Open SSMS and execute the queries mentioned below:

ALTER DATABASE (Database Name) SET EMERGENCY;

GO

ALTER DATABASE (Database Name) Set Single _User;

GO

DBCC CHECKDB (Database Name), REPAIR _ALLOW_ DATA_ LOSS) WITH ALL_ ERRORMSGS;

GO

ALTER DATABASE (Database Name) SET Multi _User;

GO

2. Mark Database in Emergency mode, the main database is detached and again re-attached

In this mode, we have to first work on Emergency mode.

ALTER DATABASE (Database Name) SET EMERGENCY;

ALTER DATABASE (Database Name) Set Multi _User;

EXEC sp_detach _db (Database Name)

EXEC sp_attach_single_file_db @DB Name = '(db Name)' @physname= N'(mdf path)

This query will get rid of corrupt log and build a new one automatically.

Concluding Lines

We have clearly discussed in this blog how important SQL database is for users and how one can recover SQL Server database in pending state.

tools-security