Due to temporary disk availability issue, SQL Server database is in “Recovery Pending“ Status.
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.
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.
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’.
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.