Sep 15 2022
Sql Database is in recovery mode 

How to fix Recovery Pending State in SQL Server Database? 

Priyanka Chauhan Updated on October 16, 2018 Database Recovery 22 Comments  

Summary:  In this Post, We are going to see that how we can fix Recovery Pending State in SQL Server Database. An SQL DBA is expected to know the answers to all database troubles. However, SQL Databases can be hard to manage, especially if they are facing technical issues. One of the trickiest SQL issues to resolve is the “SQL database recovery pending state” let’s look into this. 

SQL database states 

An SQL database is considered to be damaged if one or more of its core files are in the inconsistent state. Depending upon how severe the damage is, the database is marked with different states. Check the few states below: 

  • Online – If one of the data files has been damaged during a query or some other operation, the database will remain online and accessible. 
  • Suspect – If the transaction log is damaged and it prevents recovery or a transaction rollback from completion, which may cause it to fail. 
  • Recovery Pending – If the SQL Server knows that database recovery needs to be run but something is preventing it from starting. This is different from the SUSPECT state because it can’t be said that recovery is going to fail – it just hasn’t started yet. 

The state an SQL database is in can be checked by running the following query: 

SELECT name, state_desc from sys.databases 

GO 

The output will be: 

Primary Reasons behind “Recovery Pending State in SQL Server” 

Decoding where an error originates from and what is causing it as crucial as finding a sure-shot solution to it. When database recovery is required but cannot be initiated, an SQL database is marked in Recovery Pending state. This kind of situation arises primarily when: 

  • The database isn’t cleanly shut down, that is, there is at least one uncommitted transaction active at the time the database is shut down and the log file for it has been deleted 
  • User has tried to move the log files to a new drive to overcome server performance issues but in the process, ended up corrupting the log files 
  • Database Recovery cannot be initiated due to insufficient memory space or disk storage 

Manual way to fix “SQL server recovery pending” state 

There are 2 manual ways to start Recovering Pending State in SQL Server database that has been marked in recovery pending state: 

Solution 1: Mark database in Emergency mode and initiate forceful Repair 

  1. 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 

  1. EMERGENCY mode marks the database as READ_ONLY, disables logging, and grants access only to system administrators. 
  2. This should resolve any corruption and bring the database online. The database will come out of EMERGENCY mode automatically. 

Solution 2: Mark database in Emergency mode, detach the main database and re-attach it 

  1. Execute the following set of queries: 

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]’ 

  1. These commands will cause the server to get rid of the corrupt log and build a new one automatically. 

Note: Before initiating any of these repair procedures, you should ensure that you have proper backups of the database in question. This is to have a fail-safe copy in case anything goes wrong. Also, remember that these are highly technical procedures and you shouldn’t be performing them if you are unsure or do not have proper technical expertise. 

A better approach 

Unless you’re a pro at dealing with SQL database errors, avoid worsening the problem and switch to a safe, reliable, and automated third-party solution. Use Stellar Repair for MS SQL software to repair corrupted SQL database files and bring the database back online quickly and smoothly. 

Why Stellar Repair for MS SQL? 

  1. The software is trusted by Microsoft MVPs 
  2. Stellar Data Recovery is Microsoft’s Gold Partner 
  3. The software is 100% Norton secure 
  4. Instant delivery by Email 
  5. 100% money-back guarantee 

Features  

  1. It supports MS SQL 2016, 2014, 2012, 2008 and all lower versions. With this, the SQL recovery software is compatible with Windows 10 / 8 / 8.1 / 7 / Vista / XP and Windows Server 2012 / 2008 / 2003. 
  2. The software fixes minor and major corruptions within the SQL database and helps you recover inaccessible objects from database files 
  3. The software scans corrupted database files and extracts all vital information like tables, triggers, indexes, keys, rules, and defaults 
  4. Deleted records recovery is possible 
  5. Option to save the database into New database and Live database 

Conclusion: 

We went through the manual and the best alternative methods for the scenario “Recovery Pending State in SQL Server” and we know that there can be many reasons causing an SQL database to go offline. For any SQL user or DBA, one of the important tasks is to know way around cropping up frequent SQL errors and to bring the database back online at the earliest. Thus, tools like Stellar Repair for MS SQL are indispensable for all SQL Database administrators to restore and manage their database successfully from SQL database recovery pending state. 

admin 0 Comments