This post will explain why a database operates in a RECOVERY PENDING state and show you some Methods to Fix it.
These are technical processes. If you do not have the confidence to settle this problem, you can use our emergency SQL Support to help you do it in a simple and effective way.
How to Check Database State?
A Microsoft SQL Server database is always in one specific state.
Identifying the database state and how a database can be moved between these different states will help you troubleshoot and fix many database problems and issues.
These states include ONLINE, OFFLINE, RESTORING, RECOVERING, SUSPECT, EMERGENCY, and Recovery Pending.
You can quickly check the current state of an SQL database running the query below. The current state of a database can be verified by state_desc.
SELECT name, state_desc from sys.databases
The output should like the GIF below:
Reasons behind Recovery Pending State in SQL Server
If your database is stuck in a RECOVERY PENDING state, this means that the recovery process failed.
The good news is that probably the database is not damaged. However, It will be unavailable for user access and require further user action to fix the error and let the recovery process be completed with success.
There are many reasons for the occurrence of the Recovery Pending state. You can see a few of them below:
- SQL Server shut down due to a hardware failure, power failure, or other reasons. When a database is not shut down properly, one or more unfulfilled transactions were active during the shutdown and the transaction log file was deleted.
- Insufficient disk storage or memory space. In these cases, the database recovery cannot be started.
- Corruption of the log files. Migrating the log files to a new drive can sometimes lead to corruption of the log files.
- MDF File Corruption. The MDF files the MDF database file gets corrupted due to many reasons such as disk subsystem issues, platform issues, hardware fault, virus attack, and so on.
How to fix ?
First, make sure that you have adequate backups of the database in question before starting any repair process.
These are technical processes. If you do not have the confidence to settle this problem, you can use our emergency SQL Support to help you do it in a simple and effective way.
The best place to start the investigation is the SQL Server Error Log.
Method 1 – Set Database state to ONLINE
In cases of the database recovery cannot be started due to Insufficient disk storage space:
- First, increase the disk space.
- Set the database to mode “Online”;
- Run CheckDB (without Repair option).
You can also execute the following script (replace the string DATABASENAME with your database name):
ALTER DATABASE ‘DATABASENAME’ SET ONLINE;
DBCC CHECKDB(‘DATABASENAME’) WITH NO_INFOMSGS;
If the command has been completed without warning, the database does not need to be repaired.
Method 2 – Rebuilding the log Files (Emergency Mode)
In case the error log shows that the database has not recovered successfully due to a database missing Log file that may be deleted, renamed, or corrupted:
- First, put the database in emergency mode.
- Detach the database (Take it OFFLINE);
- Re-attach it (bring it online).
The following script executes all the steps above (replace the string DATABASENAME with your database name):
ALTER DATABASE [DATABASENAME] SET EMERGENCY;
ALTER DATABASE [DATABASENAME] set multi_user;
EXEC sp_detach_db '[DATABASENAME]';
EXEC sp_attach_single_file_db @DBName = '[DATABASENAME]', @physname = N'MDF_FILE_FULL_PATCH';
The system will automatically remove the corrupted logs and will build a new one.
Method 3 – Initiate DBCC CheckDB with repair options
If any methods help you resolve the database in recovery pending state issues, you can try performing the repair.
We only recommend the REPAIR options only as a last resort. Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB.
The DBCC CHECKDB with any of the REPAIR options is completely logged and recoverable. So, follow the best practices and use CHECKDB with any REPAIR options within a transaction (execute BEGIN TRANSACTION before running the command). Thus, you will be able to Rollback the repair operations if necessary.
There are different repair levels. Try to begin with “REPAIR_REBUILD”, which performs repairs that have no possibility of data loss:
- Set the database has to single user mode;
- Run DBBC CHECKDB with “REPAIR_REBUILD”;
- Set the database to “Online” and re-activate multiple user mode;
You can use the following script executes all the steps above (replace the string DATABASENAME with your database name):
ALTER DATABASE AdventureWorks2019 SET SINGLE_USER;
DBCC CHECKDB (DATABASENAME,REPAIR_REBUILD) WITH NO_INFOMSGS,ALL_ERRORMSGS;
ALTER DATABASE [DATABASENAME] SET ONLINE;
ALTER DATABASE [DATABASENAME] SET MULTI_USER;
If the repair is successful, the database may be set back to multiple user mode. Otherwise, repair level “REPAIR_ALLOW_DATA_LOSS” is the next option.
As the name suggests, this may result in a loss of data. Again, make sure that you have backups and understood the risks involved.
- Set the database to Emergency mode;
- Run DBBC CHECKDB with “REPAIR_ALLOW_DATA_LOSS”;
- Set the database to “Online” and re-activate multiple user mode;
The script below can be used to execute all the steps (replace the string DATABASENAME with your database name):
ALTER DATABASE [DATABASENAME] SET EMERGENCY;
ALTER DATABASE [DATABASENAME] SET SINGLE_USER;
DBCC CHECKDB (DATABASENAME,REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS,ALL_ERRORMSGS;
ALTER DATABASE [DATABASENAME] SET ONLINE;
ALTER DATABASE [DATABASENAME] SET MULTI_USER;
Are you experiencing problems with SQL Server? We can help with these problems in a quick, efficient, and affordable way. Do you need SQL Server DBA support? Our SQL Server support team is ready to assist you when and where needed the most.
One Response
what type of issues occurs doing im place upgradation