What causes a SQL Server recovery pending state?

If your database is stuck in a ‘recovery pending’ state, there are a few things that could have caused this.

  • Sudden power failure. Loss of power can leave the database in a recovery state if data was being written to a row. This sudden loss of connectivity can cause database issues if data was practically written and then interrupted.
  • Hardware failure. Similar to power failure, this causes an improper shutdown of the live database that often leaves queries and modifications in a broken, half-completed state.
  • Lack of memory. SQL servers can utilize caching and buffer pools to operate on limited memory. However, if memory resources are stretched too thin, this can cause problems in the database file and result in a ‘recovery pending’ error.
  • Log file corruption. Corruption in the log file is a common cause for a pending recovery status. Unexpected shutdowns, faulty memory, and limited storage can cause issues with the transaction log file that result in a pending state.

There are a number of different types of states a database can be in. Heavily damaged databases are normally found in numerous inconsistent states, where the primary files of the database are unable to function.

Even if your SQL database is stuck in a ‘recovery pending’ state, it’s important to know the additional states in case it changes during the repair process. The primary states of a database are as follows:

  • ONLINE – This is the normal healthy state of the database and indicates that files are accessible to users.
  • OFFLINE – The database is inaccessible and offline due to action taken by the user. The database will remain offline until it is manually brought back online.
  • SUSPECT – The primary filegroup of the database may be damaged, and could not be recovered during startup. This leaves the database in an unavailable state.
  • RECOVERING – The database is in the process of recovering. If successful, the database will return to an online state and become accessible. If this process fails, the database will return to a suspect state and remain unavailable.
  • EMERGENCY – When set to emergency, the database is put into a single-user mode where access is limited to read-only, and accessible to only accounts that belong to the sysadmin role. This state is primarily used for troubleshooting and manual recovery processes.
  • RECOVERY PENDING – The server has encountered an error during the recovery process. Missing files or system resource issues could be preventing the recovery from completing. The database is unavailable in this state.

If you’re having problems verifying that the state of your database has changed, you can check the current state by selecting state description (state_desc) while in catalog view, or check the Status property under the DATABASEPROPERTYEX function.

How to fix SQL database recovery pending

There are a few ways we can attempt to identify and fix what is causing the recovery pending error. Before making any changes to the database make sure you take a complete backup and move it to another server.

Running A Forceful Repair

The steps below will force the database into an EMERGENCY state and force a repair command. If successful, this will bring the database into an accessible state and automatically transition out of EMERGENCY mode.

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

Reconnecting The Main Database

The next method disconnects the database and then reattaches it again. This can help resolve SQL recovery pending issues as well as provide a clean log file if the current log is corrupted or inaccessible. If the steps above didn’t work, try these next.

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

Using SysTools SQL Database Recovery

If both manual methods do not recover the database from its pending recovery state, the next best option is to leverage the aid of a SQL recovery tool. In this example, we’ll be using SysTools SQL Database Recovery.

SysTools supports almost all versions of SQL server and features a handy preview tool that allows you to browse both the corrupted and health elements of your database file without being mounted to the SQL environment.

You can download and use the tool for free to see what data is considered recoverable.

Even when using recovery tools it’s important to ensure you have an additional backup of the data in case anything goes wrong.

Download Systools SQL Database Recovery

Navigate to the SysTools SQL Recovery download page and download the software. You can go through all of the steps with the free version, however, you will not be able to actually recover the data unless you purchase the software. The software offers a 30-day refund, but you should ensure your data is actually recoverable using the demo version first.

After running the executable and launching the software a simple main screen displays. Here you can view your corrupted and recovered data, import and export database files, and navigate the database structure.

Under the Open tab, navigate to the corrupted MDF file and import it into the program. You’ll have the option to choose a scanning mode. For most cases, Quick Scan will be able to identify and repair most issues. If this is not the case an

Advanced Scan can be done. This may take significantly longer spending on the size of your database objects. Next, choose your version of SQL Server below, or check the “Auto detect SQL server” box. This will quickly scan the file and associate it with its proper server environment.

If you have secondary NDF files that are included with the database you’ll have the option to include them as well. There is an additional checkbox to recover deleted items. Only check this if you’re looking to recover accidentally deleted objects.

After clicking OK a scan will start on the file. Systools begins identifying problems and detecting the recovery pending status error. You can keep tabs on the progress through the interface and even see how many tables, rules, triggers, and functions it has identified.

A notification will appear upon completion letting you know the scan is finished. It prompts you to run an advanced scan if you’re still missing data or experiencing the recovered pending status. The data will immediately be visible giving you the option to check through the tables and objects.

If you still see problems with your data or evidence of corruption, restart the process, and run an Advanced Scan. If the data looks good you’ll be able to export it as either a SQL Database or as a server compatible script.

Exporting As A Database

Choose the SQL Server Database option under the Export tab. Enter your server name if it isn’t detected automatically, and then provide a method for authentication if that is needed for your database file. You can choose to authenticate with domain Windows credentials or SQL Server Authentication.

Choose the option Create New Database and give it a name. Once again you’ll be able to preview and navigate the database file within the recovery windows. There are also additional options to export with schema and data, or only schema. Lastly, you can tell Systools whether or not you want to include any objects and records that were recovered from deleted objects.

Alternatively, you can follow the same steps but choose to export this to an existing database that overwrites the corrupted data.

Next, let’s specify the Collation Settings you want to use for the data, and check the preview box below to ensure it looks correct. When you’re ready, click Export to start the process. The status of the exported records will appear and list out the record count of all table names alongside their status.

Once completed you can save the export details as a CSV file and quickly open it to double-check that the tables and data are correct. This is a nice feature if you’re not quite ready to launch SQL Server.

Exporting As A Server Compatible Script

Files can also be saved and exported as a .SQL server-compatible script. Choose the SQL Scripts option under the Export tab and use the file navigator to select the parts of the database you wish to recover.

After choosing your recovery options you’ll be prompted to pick an export path. When the export is completed you’ll receive a notification along with steps detailing how to recover from your script.

Follow these steps to recover your database from the script:

  • Run the UserDefinedDataTypes.sql. This creates the exported database
  • Run every TableName.sql file in the export. This generates each table and it’s records.
  • Execute Stored_Procedure.sql and Views.sql.

Once completed you can run the database batch file in command prompt using the following syntax.

[ ]

If you’re still struggling to find a tool that works for you, be sure to review our seven best SQL recovery tools for further assistance.

How to avoid server recovery pending errors

Hopefully, at this stage, you’ve been able to restore your files and get your database back into an online state. Let’s review some steps you can take to avoid having your database enter a recovery pending state again.

Ensure your host is on a battery backup unit. If power were to fluctuate or fail completely, you want your server to have enough power to stay on long enough to be shut down properly. While this isn’t ideal, it does help avoid sudden corruption when the database is interrupted in the middle of serving a query. A better but more expensive alternative is to have a separate power source such as a backup generator.

Monitor your SQL environment for troublesome events. Reacting to events such as low disk space and maxed memory is never ideal. By using tools such as Manage Engine Application Monitor you can configure monitors to alert on creeping disk space, expanding log files, and overall resource utilization. This not only monitors the physical environment but also helps avoid the database from entering a recovered pending state due to lack of resources.

Backup and test your database regularly. Incremental backups help you recover from dropped tables and sudden corruption that can happen with large databases. Ensure you’re both backing up and testing to eliminate downtime and give yourself peace of mind.

  • An uncommitted transaction during a server crash
  • The database file has become corrupted
  • The transaction log has hit its configuration size limit