Table of Content
    SQL Database Repair

    How to Fix Recovery Pending State in SQL Server Database?


    Table of Content

      Summary: You may see the Recovery Pending state in MS SQL Server if there is a problem with the SQL database or due to any other reason. This post will outline the reasons behind the Recovery Pending state of SQL database and mention the methods to fix the problem. It will also mention an advanced SQL repair software that can come in handy if the issue is due to corruption in the database file.

      MS SQL database goes into the Recovery Pending state when something prevents the MS SQL Server from starting the database recovery. This might happen due to resource-related issues, missing files, or problem with the database. When the server marks the database in Recovery Pending state, the database is not available for users. In this post, we’ll discuss the possible causes for the Recovery Pending state of MS SQL Server database and the solutions to fix the issue.

      Reasons behind Recovery Pending State in SQL Server

      SQL Server database may go into the Recovery Pending mode due to one or more of the below reasons:

      • The database didn’t shutdown properly.
      • Missing or corrupted transaction log files.
      • Disk/drive, where the database is saved, is out of storage.
      • Database file is corrupted or damaged.
      • System is terminated or crashed abnormally.
      • Bugs in MS SQL Server.

      Methods to Fix Recovery Pending in SQL Server Database Issue

      First, you can check the SQL Server error log. This will help you detect any issues that might be responsible for the Recovery Pending state of the database. To check the error logs, open the SSMS, expand the SQL Server Agent, and click Error Logs. If you didn’t find any issues, then follow the below troubleshooting methods to bring the SQL database to normal mode.

      1. Turn OFF the AUTO CLOSE Option

      Sometimes, the database goes into the Recovery Pending state if the AUTO CLOSE option is ON for the database. When this option is turned ON, SQL Server closes the database after the use and turn it ON next time when a user accesses the database. So, if the database is frequently opened and closed after each connection, it is recommended to turn OFF the AUTO CLOSE option. To do so,

      • Open SQL Server Management Studio (SSMS) and connect to the SQL Server Instance.
      • Right-click on the database and select Properties.
      • On the Database Properties window, click on Options in the left pane.
      • Then, set the value as FALSE for AUTO CLOSE and click OK to save the changes.

      2. Recreate the Log Files

      SQL database can go into Recovery Pending state if the transaction log files are missing or corrupt.  You can recreate the log files by detaching and re-attaching the main database. When you detach the database (take the database offline) and then bring it online (re-attach), it rebuild the log files automatically. Here’s how to detach the SQL database:  

      Note: You can’t detach the database which is published, replicated, suspect, or in a mirroring session.

      • Open SQL Server Management Studio (SSMS) and click the New Query option.
      • Set the database in EMERGENCY mode by using the below command:
      ALTER DATABASE [DBName] SET EMERGENCY;
      • Next, change the database to multi_user mode by using the below command:
      ALTER DATABASE [DBName] set multi_user
      • Now, run the below command to detach the database:
      EXEC sp_detach_db ‘[DBName]’

      Once the database is detached, run the below command to reattach the SQL database:

      EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’

      3. Repair the Database

      SQL Server may fail to start the database recovery if there is corruption in database (MDF/NDF) files. You can repair the database by using the DBCC CHECKDB command. Here’s how to use this command.

      First, check the database for any inconsistencies or errors by running the below command with NO_INFOMSGS:

      DBCC CHECKDB(database15) with NO_INFOMSGS;

      The above command will display the consistency errors (if any) in the database without information messages and also recommends the repair options.

      If the DBCC CHECKDB command shows any issues in the database, then you need to repair the database. For this,

      Note: Before initiating the repair procedure, make sure to take a backup of the database.

      • Set the database in EMERGENCY mode by using the below command:
      ALTER DATABASE [DBName] SET EMERGENCY;
      GO
      • Then, execute the below command to set the database to SINGLE USER mode.
      ALTER DATABASE [DBName] set single_user
      GO
      • Now, run the DBCC CHECKDB command with the ‘REPAIR_ALLOW_DATA_LOSS’ option (as given below) to repair the database.
      DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
      GO
      • When the repair process is finished, change the database to multi-user mode again by running the below command:
      ALTER DATABASE [DBName] set multi_user
      GO

      An Alternative Solution to Repair SQL Database

      Running the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS can result in loss of data. To prevent data loss and to quickly repair the corrupt database, you can use an advanced SQL database repair software, like Stellar Repair for MS SQL. It is a specialized software that can repair corrupt database and help bring the database back online from the Recovery Pending state. It can recover all the data from corrupted NDF/MDF files with complete integrity and precision.

      Key Features of Stellar Repair for MS SQL

      • Repairs corrupt and inaccessible MS SQL database (.mdf and .ndf) files.
      • Recover all the database components, like tables, keys, indexes, triggers, rules, stored procedures, etc.
      • Recover deleted records.
      • Supports MS SQL 2019, 2017, 2016, 2014, 2012, 2008, 2008 R2, and lower versions.
      Free Download for Windows

      Steps to Use Stellar Repair for MS SQL Software

      • Download, install, and run Stellar Repair for MS SQL software.
      • From the Select Database window, click Browse or Search to select the database file you want to repair. 
      select database file
      • You will see two options – Standard Scan and Advanced Scan to scan the corrupt database. Select the Standard Scan mode. If the database file is highly corrupted, then click on Advanced Scan. Click OK.
      Scan Corrupt MDF File by Standard Scan Mode
      • Click Repair to start the repair process.
      • Once the repair process is complete, the software will show all the recoverable items in the database.
      Preview of the repaired MS SQL database objects
      • To save the repaired file and its components, click Save on the File menu. 
      Saving option to save repaired SQL database
      • On the Save Database window, do the following:
      • Choose MDF under Save As.
      • Select New Database or Live Database under Saving Option.
      • Enter the required details in the Connect To section.
      Authentication options for connectivity
      • Click Browse to choose the destination for saving the repaired file.
      File Saving Options
      • Click Save.
      • When the ‘Save Complete’ message box appears, click OK.
      Save Complete Window

      The repaired file will be saved at the selected location.

      Conclusion

      SQL database can go into Recovery Pending state due to several reasons. In this post, we’ve explained how to fix the Recovery Pending state and bring the database back online. If the Recovery Pending mode of the database is due to corruption, then you can use Stellar Repair for MS SQL software to repair the database. It can repair the database (MDF/NDF) file and restore all the objects with complete integrity. It can also help you fix several corruption-related errors in SQL database.

      Was this article helpful?

      No NO

      About The Author

      Charanjeet Kaur linkdin

      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.

      29 comments

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

        Help me to resolve my issue of (Recovery Pending…) for my database.

        Thanks

      2. I tried manual methods to handle recovery pending state and now, the database in suspect mode. Please suggest….!

        1. Hello Noel,

          As database in the suspect mode so, you can download Stellar SQL database repair software and repair the MDF file. The software brings the SQL database from suspect mode to normal mode.

          Good Luck!

      3. Your blog saved me!!
        The SQL recovery software done the miracle and repaired my database successfully.

      4. On running SELECT name, state desc from sys.databases, I was getting RECOVERY_PENDING. Even after running multiple T-SQL commands and even the commands which you recommended, I’m not able to get my .mdf back in order.

        Are you sure Stellar Repair for MS SQL would be able to resolve my database which is showing RECOVERY_PENDING issue?

      5. After reading the review of MVP (Damian Widera), I can test the software to fix our ongoing SQL server database issue.

      6. My SSMS 2016 too was showing recovery in pending state and even after troubleshooting it for 12 hours straight.

        I was not able to detect the error but thankfully with the help of your SQL reference. I found out that the space on the Hard Drive was not enough.

        Thanks for the help,

        Best Regards,
        Neil Richards

      Leave a comment

      Your email address will not be published. Required fields are marked *

      Image Captcha
      Refresh Image Captcha

      Enter Captcha Here :

      Related Posts

      WHY STELLAR® IS GLOBAL LEADER

      Why Choose Stellar?

      • 0M+

        Customers

      • 0+

        Years of Excellence

      • 0+

        R&D Engineers

      • 0+

        Countries

      • 0+

        PARTNERS

      • 0+

        Awards Received