Troubleshooting “SQL Server Database Is In Recovery Pending State”

Overview

Users of SQL Server Database must be aware of Recovery states, that occurs when there is unintentional dropped SQL table or any other components that needs to be restored. The SQL Server Database is in recovery pending state at situations like restart of SQL Server, offline & online state of database or while restoring database from a backup. However, if there is any issue during this recovery process, error can be encountered showing SQL Server Database is in Recovery pending state. In this content, we will learn a method to bring back the database from Recovery pending state.

Errors Associated with Database in Recovery Pending State

As SQL Server database is being used in multiple organisations, any user may encounter that their database is in recovery pending state due to many reasons. In some scenarios, it is hard to bring back database online from this state. Some of the errors faced by users during this procedure are displayed below:

In the above error, it says that primary file of database does not match with one or more files. In addition, there is a chance of database files being corrupted.

This error can be displayed when there are issues in database compatibility levels and permissions. It may occur while trying to attach database from an earlier SQL Server version with the newer instance of SQL Server.

One reason for the issue in recovery mode of SQL Server database is due to insufficient memory space or disk. This error is associated to the same where database cannot be opened due to inaccessible files and insufficient storage. Error log details of SQL server needs to be checked.

In this error, log files do not match with the main SQL Server database file. Possible reason is the log files are associated with another database or it has been rebuilt earlier.

Way to Bring Back SQL Server Database Online

As discussed above, one primary reason for the database in recovery pending state is due to log files. Many times, databases must have been involved in SAN crash that is why migration to a new disk is needed. It may make the system run 10 times slower than it was running earlier. To overcome such situation, user try to build a new drive for the logs, move them and alter the file location stored in master database. All these operations can make the log file corrupt.

Following are the steps that need to be considered while dealing with database in Recovery pending state:

1. We will set the status of the SQL Server database to emergency mode using the following command
   ALTER DATABASE [DBName] SET EMERGENCY
2. Set the SQL Server Database in multi-user mode using the command below
   ALTER DATABASE [DBName] SET MULTI_USER
3. Detach the main SQL Server database
   EXEC sp_detach_db ‘[DBName]’
4. Re-attach the same SQL Server database again running this command
   EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’

The main thing that has been focused is to get rid of the corrupt log and to make the SQL Server build a new one.

Tips for Recovery Pending State

• It is advisable to use sp_attach_single_file_db ONLY on those data files detached using sp_detach_db. Use the command sp_detach_db instead of using GUI.
• The MULTI_USER mode should be set on the SQL Server database before detaching the database. If the data file is read only, SQL Server cannot build a new log file. The SQL Server Database should not be detached in SINGLE_USER mode.
• It is necessary to take a backup of the databases always by users themselves. Do not depend on Database administrators for the backup purpose.

Conclusion

Due to reasons like large size of transaction log files, SQL Server restarted during long running transaction, multiple virtual log files, bug in SQL Server, etc. SQL Server database may get into Recovery Pending state. This is the phase where database needs to bring back online in consistent state. The content defines some of the common errors encountered by users while trying to bring back database online. Additionally, it also defines some ways to deal with SQL Server database that is in Recovery pending state. In case, if you want to avoid facing such probelems then one can go with a third party SQL Database Repair tool to fix such issues quickly.