SQL server database is a widely used database because of its advanced and well-organized features. It helps maintain data integrity in a secured mode. But from time to time, you’re going to come across some roadblocks, which might disrupt your workflow. In this article, learn what the “Recovery Pending” state is and how to resolve it.
Type of States in SQL Server Database
There are different types of SQL server database. For the database to work perfectly, the core files should be in the same consistent state. If there’s any inconsistency, then the SQL database is said to be damaged.
The database state tell you how severe the damage is. Some of the SQL states are:
Online - The Online state means that the database is online and accessible. It might be that one of the database files is damaged when executing an operation.
Suspect - If the server is marked as Suspect, then it means the database was not recovered when the SQL Server was starting.
Recovery pending - When the database server wants to start database recovery but isn’t able to for any reasons, it will show the Recovery Pending state.
Please note that Recovery Pending is not the same as “Suspect” state. In suspect, the server doesn’t know if the recovery is going to fail. In Recovery Pending, the recovery process just hasn’t begun yet.
You can find the current state of any SQL database using this command -
It will show you a row of database states.
Why Recovery Pending Status Occurs?
As already mentioned, you see Recovery Pending status when the SQL server fails to start the recovery process. But why exactly does that happen? Because of any of the following reasons:
There are two ways you can fix this issue. Below we explain each one of them.
Method 1 - Resolve the Issue Manually
The first method is a manual method. You need to manually mark database to Emergency Mode and begin a forceful repair process.
When you do so, the database gets marked as “READ_ONLY”. It also disables logging and only allows system admins to access the database.
Open the db in emergency mode and use the DBCC CHECKDB command to repair the database. You’d have to use this command for this purpose - “REPAIR_ALLOW_DATA_LOSS”.
Here are the steps involved:
That should fix the issue.
Another way to resolve the issue manually is to mark the database in Emergency Mode and then detach the main database. After some time, re-attach the database.
So open SSMS and execute the command:
Corrupt logs will be removed from the server and create a new one.
Using Stellar Repair for MS SQL
Another way to fix the issue is to use Stellar Repair for MS SQL. This software repairs database files that have become corrupt. The database then can recover from Recovery Pending state.
Follow the steps to use this software to fix the issue:
If you still can’t fix the issue after using the methods above, get in touch with the hosting provider.
Type of States in SQL Server Database
There are different types of SQL server database. For the database to work perfectly, the core files should be in the same consistent state. If there’s any inconsistency, then the SQL database is said to be damaged.
The database state tell you how severe the damage is. Some of the SQL states are:
Online - The Online state means that the database is online and accessible. It might be that one of the database files is damaged when executing an operation.
Suspect - If the server is marked as Suspect, then it means the database was not recovered when the SQL Server was starting.
Recovery pending - When the database server wants to start database recovery but isn’t able to for any reasons, it will show the Recovery Pending state.
Please note that Recovery Pending is not the same as “Suspect” state. In suspect, the server doesn’t know if the recovery is going to fail. In Recovery Pending, the recovery process just hasn’t begun yet.
You can find the current state of any SQL database using this command -
Code:
SELECT name, state_desc from sys.databases
GOWhy Recovery Pending Status Occurs?
As already mentioned, you see Recovery Pending status when the SQL server fails to start the recovery process. But why exactly does that happen? Because of any of the following reasons:
- The database failed to shut down properly. There might be an uncommitted transaction that’s open when attempting shutdown. It would delete any active transaction log file.
- The user was moving the log files for better performance to a new drive but the files became corrupt in the process.
- There’s insufficient memory or disk storage space in the server which is preventing database recovery.
There are two ways you can fix this issue. Below we explain each one of them.
Method 1 - Resolve the Issue Manually
The first method is a manual method. You need to manually mark database to Emergency Mode and begin a forceful repair process.
When you do so, the database gets marked as “READ_ONLY”. It also disables logging and only allows system admins to access the database.
Open the db in emergency mode and use the DBCC CHECKDB command to repair the database. You’d have to use this command for this purpose - “REPAIR_ALLOW_DATA_LOSS”.
Here are the steps involved:
- Open SSMS
- Execute the following commands :
Code:
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
GOAnother way to resolve the issue manually is to mark the database in Emergency Mode and then detach the main database. After some time, re-attach the database.
So open SSMS and execute the command:
Code:
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]’Using Stellar Repair for MS SQL
Another way to fix the issue is to use Stellar Repair for MS SQL. This software repairs database files that have become corrupt. The database then can recover from Recovery Pending state.
Follow the steps to use this software to fix the issue:
- Download the Stellar Repair for MS SQL from official website.
- Install and run it on your computer.
- Open the “Select Database” window
- Select the database file that needs repairing by clicking on Browse. Alternatively, you can search for it too.
- You can either select the “Standard Scan (Recommended)” option or “Advanced Scan”. Select the one you’re comfortable with.
- Finally, select “Repair” to initiate the process.
- You’ll see a list of recoverable db components.
- From the File menu, click on “Save” which would save the repaired file.
- Under Save As option, select MDF
- For the Saving Option, you can either select Live Database or Live Database. It’s preferred to create a new database.
- For the Connect to option, provide necessary details for both Server Name/Instance Name and Authentication.
- Click on Browse to select the destination where you want to save the repaired file.
- Finally, click on Save.
If you still can’t fix the issue after using the methods above, get in touch with the hosting provider.
