Summary: This post will outline the reasons why a database (db) is marked in recovery pending state. Also, it will describe methods to fix ‘SQL server database in recovery pending state’ problem. You can resolve the problem by executing queries in SQL Server Management Studio (SSMS), or by using Stellar Repair for MS SQL software.
A SQL database is considered to be damaged if one or more of its core files are in an inconsistent state. Depending on how severe the damage is, the db is marked with different states. Some of these states are:
Let’s first understand how you can check the current state of the database.
To check the current state of an SQL db, run the following query:
SELECT name, state_desc from sys.databases GO
Executing the query will give you an output that looks something like:
Some of the reasons causing such an issue are:
Note: Before initiating any of the following repair procedures, make sure to take db backup, so as to have a fail-safe copy in case anything goes wrong.
Following are the two manual ways that helps bring SQL database in recovery mode to normal mode:
Database EMERGENCY mode marks the database as READ_ONLY, disables logging, and grants access only to system administrators. Essentially, setting the db in this mode can bring the inaccessible database online.
Note: Usually a database comes out of EMERGENCY mode automatically. If you are having issues restoring the db from this mode, read this Recover SQL Database from Emergency Mode to Normal Mode.
Once you have opened the db in EMERGENCY mode, try repairing the database using the DBCC CHECKDB command with the ‘REPAIR_ALLOW_DATA_LOSS’ option. To do so, open SSMS and execute the following set of queries:
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 GO
This solution also requires to mark db in EMERGENCY mode. Once done, take the database offline (detach) and then bring it online (re-attach). To do so, execute the following set of queries in SSMS:
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]’
The above set of queries help make the server get rid of the corrupt log and build a new one automatically.
If the above methods cannot help resolve the db in recovery pending state issue, use Stellar Repair for MS SQL software. The software can help you repair corrupt database files and bring the database back online from recovery pending state quickly and smoothly.
The repaired file will get saved in the selected location.
Note: For detailed information about using Stellar Repair for MS SQL software, refer to this user guide.
This post outlined reasons behind ‘db in recovery pending state’ issue like database is not cleanly shut down, database files (.mdf or .ndf) turned corrupt, insufficient memory or disk space. It also described methods to fix Recovery Pending State in SQL Server Database.
You can fix the database by setting it to emergency state, and initiating the repair process or de-attaching and reattaching the db. But, such solutions have their own downsides:
You can overcome these limitations by using Stellar Repair for MS SQL software. The software helps SQL users and database administrators (DBAs) to fix severe database corruption errors. Also, it ensures complete recovery of db components, while preserving data integrity.
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.