Introduction

In this article, we will learn how to restore a database which is stuck in the Recovery Pending State. Recently, due to power failures, the disk which contains the transaction log file was failed. Due to that, some of the development databases were entered into the recovery pending state.

To fix the issue, we rebuild the transaction log files and bring the databases online. In this article, I will explain the entire process step-by-step. But before that, first let us understand common reasons when the database entered recovery pending state.

Causes of Recovery Pending State

After restarting the SQL Server services, SQL server database can be in recovery pending state when the database is unable to complete or start the recovery process. The reason can be the unavailability of resources, insufficient permissions or any corruption identified during the recovery process.

Here are some common reasons due to which the SQL Server database might go into Recovery Pending state.

Insufficient disk space

During recovery process, if the transaction log does not have adequate space to grow, the database might go into the recovery pending state.

Corruption in the MDF/LDF files

Sometimes there is corruption in transaction log files or data files of the database, the SQL Server services might abruptly shut down. When we try to start the services, the SQL Server database will report a corruption and set the database in recovery pending state.

Missing transaction log file

The database is unable to find or access the transaction log file because it is missing, or the user does not have adequate permission to access the database files. In such cases, the SQL Server database will go in the recovery pending state.

SQL Server service interruptions or crashes

Sometimes the SQL Server services abruptly shut down due to bugs or storage or power failure which will cause corruption in log file or data file. And due to corruption in database files, when SQL Server attempts to start, the database will go in the Recovery Pending state.

In this article, we will learn how to recover a database whose transaction log file is missing.

Simulate failure

In this article, we will learn how to fix a database which is in recovery pending state due to the transaction log file getting missing. To understand the process first, we will try to simulate the failure.

I am performing this operation on my laptop. Please do not attempt this on production database servers. I have created a database named StudentMasterDB and a table named Mst_tblStudent. The query to create database and table is below.

/*Script to create the database*/use master
GO
CREATE DATABASE [StudentMasterDB]
GO
/*Script to create the table*/CREATE TABLE Mst_tblStudents (
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE NOT NULL,
Gender CHAR,
EnrollmentDate DATE NOT NULL,
Class VARCHAR(20),
ContactNumber VARCHAR(15),
Address VARCHAR(255),
IsActive INT
);

I have added some sample data to it. First, let us initiate a transaction to update a record in a table. To do that, run the following query. Do not commit the transaction.

USE [StudentMasterDB]
GO
BEGIN TRAN
UPDATE Mst_tblStudents SET GENDER = 'M' WHERE CLASS='I'

Once query is executed, stop the SQL Server services using following command in PowerShell.

net stop MSSQLSERVER

Now, delete the transaction log file and start the SQL Server services.

Remove-Item -Force "C:\MSSQL\Log\StudentMasterDB_log_1.ldf"
net start MSSQLSERVER

Once services are started, execute the following query to check the database state.

SELECT name, database_id,state_desc FROM sys.databases
WHERE database_id>5 and name='StudentMasterDB'

Output

As you can see from the above screenshot, the database state is in recovery pending. Open SQL Server error log to view the details. Here is the screenshot of error log file.

Let us understand what happened.

In normal scenario, when SQL Server started, it goes through the recovery phase of transaction log. When SQL Server finds any open transaction, it attempts to roll back it to maintain the database consistency. But as we have deleted the transaction log file, the SQL Server was unable to find it and hence it marks database in RECOVERY_PENDING state.

Now let us understand how to fix the issue.

Steps to resolve the issue

To fix this issue, we must rebuild the transaction log file of SQL Server. To do that, execute following steps.

Step 1: Put the database in emergency mode.

First, we must put the database in emergency mode. The emergency mode will allow us to access the database with limited functionality. To do that, run following query

USE master
GO
ALTER DATABASE StudentMasterDB SET EMERGENCY

Step 2: Perform database consistency check.

This is an optional step. If the database is in RECOVERY_PENDING state due to corruption, you should perform a consistency check.  To do that, run the following query.

DBCC CHECKDB ('StudentMasterDB')

During consistency check, if any corruption is identified, you must perform the emergency repair. You can read recover MS SQL database from suspect mode article to learn the process to restore the database from corrupted transaction log file (Emergency mode repair).

In our case, we have deleted the transaction log file hence I am skipping the step.

Step 3: Rebuild the Transaction log file

We can use the following query to rebuild the Transaction log file.

ALTER DATABASE [Database_Name] REBUILD LOG ON (NAME = logical_file_name, FILENAME = 'path');

In the syntax.

1. Database_name: Specify the database name whose transaction log you want to rebuild.
2. Logical_file_name: Specify the logical name of transaction log file.
3. Path: Specify the location of the transaction log file.

In our case the query to rebuild the transaction log is as follows:

ALTER DATABASE [StudentMasterDB] REBUILD LOG ON (NAME = student_master_log_file, FILENAME = 'C:\MSSQL\Log\student_master_log_file.ldf');

Step 4: Set database online

Once log is rebuilt, execute the following query to mark database ONLINE.

ALTER DATABASE [StudentMasterDB] SET ONLINE

Once the above steps are completed successfully, execute following query to verify the database status.

USE master
go
SELECT database_id, name, state_desc FROM sys.databases WHERE name='StudentMasterDB' 

Query Output

As you can see from the above screenshot, the database is online.

Note that rebuilding the transaction log led to the loss of all uncommitted transactions. To avoid that always restore the database from the backup.

Prevention Tips

Here are a few tips that help to avoid such issues.

1. Establish efficient backup and restore strategy to ensure minimum data loss and downtime.
2. Always monitor the disk space and storage especially when you are performing any operations which can fill the transaction log or data file rapidly. Also, set up necessary alerts so we get notified before the issue occurs.
3. When we move the data file or log file to a different location or directory, make sure the user has administrative access on the new directory or disk.
4. Always document the steps of database recovery so in case if disaster occurs, we can quickly bring database online.

Conclusion

In this article, we learn about the possible reasons for SQL Server database going into the Recovery Pending state. We learned how to put the database in recovery pending state by simulating a failure which is caused by deleting the transaction log file.

We also learned the step-by-step process to rebuild the transaction log to fix the issue and set bring database online.

Fixing the database state from recovery pending to online can be challenging. Especially when corruption occurs in specific data files. The backup and restore method take a long time and rebuilding a transaction log will cause data loss. Here I would like to recommend a data recovery tool Stellar Repair for MS SQL which provides a lot of options to recover the corrupt database. This tool is useful when you are fixing corrupted records which put database in recovery pending state.