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
[/one_third][two_third]With a team of skilled software Developers, Ujudebug is the best IT Solution Company in Assam. Fully Customized Software Development services with multiple platforms. Our award winning team will we provide the best experience to customers at an affordable price.
