SQL Server Database in “Recovery Pending“ Status

Due to temporary disk availability issue, SQL Server database is in “Recovery Pending“ Status.

Recovery Pending

Let’s step by step repair the database by the following guidelines:

  • Make sure disks are available and with enough space for databases.
  • Make a backup of datafiles and logfiles. In case repairing failure, so the alternative repairing methods can be used later against database restoration from this backup.
  • Manually set database online.
  • Run CheckDB.
DBCC CHECKDB

You can run CHECKDB with option ‘NO_INFOMSGS’ to eliminate the informational messages.

DBCC CHECKDB ('TestDatabase') WITH NO_INFOMSGS;

In this case, CheckDB has completed without warning, so the database is healthy. Otherwise continue to repair the database.

• Set to single user mode for databases to be repaired.

ALTER DATABASE TestDatabase SET SINGLE_USER;

• Start to repair the database.

First try “REPAIR_REBUILD” option.

DBCC CHECKDB('TestDatabase',REPAIR_REBUILD)

The repaired database can be se to multiple user mode now, if it is successful.
Otherwise continue to repair with level ‘REPAIR_ALLOW_DATA_LOSS’.

DBCC CHECKDB(‘TestDatabase’,REPAIR_ALLOW_DATA_LOSS); 

Please note the option name explains the potential data loss could happen.

If the repairing is successful, the database can be set back to multiple user status to open to users.

If the repairing is still a failure, then continue to next step for repairing with ‘EMERGENCY’ mode.

ALTER DATABASE TestDatabase SET EMERGENCY;
ALTER DATABASE TestDatabase SET SINGLE_USER;
DBCC CHECKDB ('TestDatabase',REPAIR_ALLOW_DATA_LOSS) WITH  NO_INFOMSGS,ALL_ERRORMSGS;

Finally set database to ‘online’ and multiple user mode.

ALTER DATABASE TestDatabase SET ONLINE;
ALTER DATABASE TestDatabase SET MULTI_USER;

Let’s check database online and normal now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.