Summary: In this Post, We are going to see that how we can fix Recovery Pending State in SQL Server Database. An SQL DBA is expected to know theanswers to all database troubles. However, SQL Databases can be hard to manage, especially if they are facing technical issues. One of the trickiest SQL issues to resolve is the “SQL database recovery pending state” let’s look into this.
In this post we are going to learn about how to change database status to ONLINE State from Recovery Pending State.As an SQL DBA it is always very important to learn known issues which can arrise anytime in the DBA liftime. DBA have to get the database to its normal state when database is changed knowingly or unknowingly.
First of all lets understand what are the states of Database.
SQL Database States
In any case when single or multiple files cannot be accessed in SQL Server that means database is corrupted. Depending upon how sever the damage is the Database’s state is defined.
- Online: Database remains Online when any of data files is corrupted during query execution.
- Suspected:: In case, the transaction log file is damaged and it is creating obstructions on recovery or preventing transaction rollback from completion, it will result in failure of SQL database.
- Recovery Pending: This happens when database knows that recovery can be done but something is preventing it to start recovering the database. This is different from Suspected State but it does not mean that recovery will fail for sure. It means recovery operating has not been started. This can be done manually too.
We can check the state of each database by running query on the system table,
sys.database on master database.Let’s select the name of the database and state of the database
SELECT name, state_desc from sys.databases
How to fix this issue
Solution : Change database to Emergency mode and initiate repair by DBCC Command
Alter Database [DatabaseName] set Emergency; GO
Alter Database [DatabaseName] set single_user; Go
DBCC CheckDb([DatabaseName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; Go
Alter database [DatabaseName] set Multi_user; Go