The SQL Server database is not used to having an error frequently. But there are some instances when there is some error. Most of the time these errors are solvable in just a few steps and rarely it affects the workflow of the user. A SQL database is only considered to be damaged when one of the core files is inconsistent in the state. It depends on the severity of the damage that marks the database with its state. Here are the few states that the database shows with its level of inconsistency:
- Online – The state remains online if there is any error while working on a query or any other operation in SQL. It can be one or more damaged files that lead to this state but even then the database s accessible to check.
- Suspect – In simple words, if the database is beyond the recovery while starting the SQL server, it is considered to be the Suspect state.
- Recovery Pending – Users get confused between Suspect and Recovery Pending state easily. According to the Suspect state, the recovery has failed but it is not the case in the Recovery pending state. Recovery Pending states are marked on the database when the SQL server knows that the database needs recovery but something is getting in the way to prevent the recovery from starting. Unlike suspect state recovery pending state knows that the recovery isn’t yet started.
Is it possible to check the current state of the Database?
Yes, it is quite possible to check in a fairly easy manner. Just need to run a query to check the current state of the database:
GO
This will open a dialog box that will show the state of different files in the database.
What are the reasons for the database in Recovery Pending state?
- Sometimes the database is not shut down properly which leads to the system realizing that there is at least one unfinished transaction that was needed to complete. This results in deletion of the currently active transaction log file and recovery is needed to get the last saved database to solve the issue.
- One of the common ways to create any issue is to transfer log files from one server to another. Many times it can bring corruption to the files that lead to this issue.
- If there is any shortage of storage in disk or memory it can cause prevention in the data recovery process to start. Hence, the database gets a recovery pending state.
Recommended: How to fix SQL Server database consistency errors?
How to fix Recovery Pending State in SQL Server Database?
It is important to understand that there are a few procedures to do before initiating repairing the database. The most important is to take back-up up the full database so that in case something goes wrong you’d still have another chance. Two manual methods can be used to fix the recovery pending state of the database. We are going to explain the step-by-step process so that it is easier for anyone to perform.
Method #1: Marking Database in emergency mode and start forceful repairing of the files
The benefit of marking the database in emergency mode is that it will mark the database as READ_ONLY, disable logging from any system, and grant permissions and admission to the system administrator only to safeguard the files and database while running the commands. Essentially, this will bring the database that is inaccessible online to make it accessible. After marking and opening emergency mode for the database the user can try the DBCC CHECKDB command along with the REPAIR_ALLOW_DATA_LOSS option to start fixing the issue. Open the SSMS and execute the following queries:
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
GO
Method #2: Mark Database in Emergency Mode, First detach and then re-attach the main database
This manual method also requires the database to be marked in emergency mode to perform the solution. After the database is marked in emergency mode, detach the database and re-attach it which can be done by executing the following queries:
ALTER DATABASE [DBName] set multi_user
EXEC sp_detach_db ‘[DBName]’
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’
These queries will get rid of the corrupt log and build a new one automatically. Hence, it is a good and safe solution.
Although these methods are quite successful and we recommend the users to perform the same, there are a few other alternatives that can help fix the pending recovery state. Some companies have introduced tools to fix the state of the database. So, if the above solutions don’t work out the users can definitely try third-party tools to repair corrupt SQL databasse as well.
Conclusion
The SQL Server database is not usually prone to errors but in a few cases, the database can be driven to the Recovery Pending state. This issue is easily solvable by understanding the state first and then performing solutions as mentioned above using the queries. If the problem is still not solved either use the methods again on the backup file or use third-party SQL Database recovery tools.
