A state specifies the current running mode of the SQL database. There are seven main states In SQL Server. The SQL database is always in one state. You can check the current state of the database by selecting the state_desc column of the sys.databases catalog view. Here’s how:

SELECT name, state_desc  FROM sys.databases

This command displays all the databases in the current instance with status.

If the database is in a Recovery Pending state, it is unavailable. You need to recover from the Recovery Pending state into an online state to access the database. In this article, we’ll discuss the common causes why the server marks the database as Recovery Pending and how to avoid them.

Causes and solutions of Recovery Pending State in SQL Server

Following are the causes behind the Recovery Pending state in SQL, along with their solutions:

Cause 1- Hard-disk is out of storage

The Database changes the state into Recovery Pending when resource-related issues like insufficient disk space prevents the SQL Server from starting the database recovery.

Solution

Make sure the hard-disk hosting the database files have sufficient disk space. You can check the total disk space left on your system. To do this, follow the steps below:

• • Open the File Explorer window on your system
  • Click This PC on the left side of the window.
  • Under Devices and drives, you will see the available space on your drive.

If the disk is full, delete the unnecessary files from the disk or use the Disk Cleanup tool to free up drive space.  Using this tool, you can delete the temp files and system files from the system. To do this, follow the steps below:

• Type disk cleanup in the search field on your system’s taskbar and press Enter
• Select the drive you want to clean up and then click
• Under Files to delete, choose the file types you want to delete.
• Click

Alternatively, you can move the database file to a drive with more space.

Cause 2- Missing, damaged, or corrupted Transaction log files

SQL database can change state to  Recovery Pending state if the transaction log files are missing, damaged  or corrupt.

Solution:

You can rebuild the transaction log files by detaching and re-attaching the main database.  Detach and then de-attach, recreate the transaction automatically. Following are the steps to detach the database in SQL:

• In SSMS, connect to the instance of the database engine and then expand that instance.
• Select and right-click on the database you want to detach.
• Click Tasks and then click Detach.
• In Detach dialog box, check the database name, drop connections and confirm other information.
• Once you’re ready to detach the database then click OK.
• Next, re-attach the database

These steps rebuild the log files automatically and help you recover the database from the Recover pending state.

Cause 3-Corruption in SQL Database file

The SQL database can go to Recovery pending state if it is damaged or corrupted. You can check and repair the SQL database using DBCC CHECKDB command. If the command checks fail and displays an error indicating issues in the database, then repair the database using DBCC CHECK DB with the REPAIR_ALLOW_DATA_LOSS repair option. For this, first set the Database to Emergency Mode to access the database.

ALTER DATABASE [Dbtesting] SET EMERGENCY

Next set database to SINGLE_USER Mode and then repair it using the below command

DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

GO

Wait till the repair is complete, then change the database to multi-user mode by executing the following command:

ALTER DATABASE [DBName] set multi_user

GO

It is to be noted that repairing database using REPAIR_ALLOW_DATA_LOSS can cause data loss as it deallocate a row or pages to resolve the errors. So, it is always suggested to perform use REPAIR_ALLOW_DATA LOSS as a last resort and take a backup of the database before using this repair option.

To avoid data loss and restore all the data from corrupted database, you can use a third-party SQL repair tool, such as Stellar Repair for MS SQL. This tool repairs the corrupt MDF/NDF file and saves the repaired file data in new database file and various other file formats, like CSV, HTML, etc. You can also save the repaired data from repaired file to a live database with complete precision and integrity. The tool supports SQL Server 2019, 2016, 2013, and earlier versions.

To Wrap Up

The SQL database change the Recovery Pending state due to multiple reasons. Above, we’ve discussed causes of Recovery Pending state and their solutions. If the database is unavailable due to corruption then use a professional SQL recovery tool like Stellar Repair for MS SQL to repair the database. This DIY SQL repair tool can repair the database file and help you bring the database back online.  It can recover all the data, including deleted objects from the corrupt database file without any data loss. The software is available for free download for evaluation purposes.

The tool helps repair SQL databases on both Windows as well as Linux machines. It supports Ubuntu 18.04 (64-bit), 16.04 (64-bit),  19.10 (64-bit), and Red Hat Enterprise Linux 7 (64-bi

Ujudebug Office

Technology Company - IT startup, Website Design, Mobile App Development, Digital Marketing, SEO, ECommerce Solution, Custom Software, Coding Community