How to Recover a Database from Suspect or Recovery Pending State in SQL Server?
So one or more of your databases went into RECOVERY PENDING
state or in SUSPECT
mode and you are confused about what to do now to fix the issue immediately.
Do not panic 😨 Almost every DBA has encountered this scenario quite a few times in their professional career where one or more databases didn’t come ONLINE
after scheduled maintenance or due to an unexpected server reboot.
I know you are probably in an incident scenario and on a tight SLA to bring the database back online. So, let’s jump into resolving the issue first and make sure to check out the reason behind it, once the database is online.
List out the databases which are not online:
For this demo, we will use a database named dbaguides
which is currently in RECOVERY PENDING
state.
Let’s verify the current state of the databases.
select name, state_desc
from sys.databases
where state_desc <> 'ONLINE'
Bring the database back online:
- Put the database in
Emergency
mode first. It puts the database inREAD_ONLY
mode, disable logging and become accessible to thesysadmins
only.
ALTER DATABASE [dbaguides] SET EMERGENCY;
GO
- Put the db in
SINGLE_USER
mode.
ALTER DATABASE [dbaguides] set single_user
GO
- Perform
DBCC CHECKDB
to recover the database.
DBCC CHECKDB ([dbaguides], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
The REPAIR_ALLOW_DATA_LOSS option may result in some data loss.
Certain errors, that can only be repaired using the REPAIR_ALLOW_DATA_LOSS option, may involve deallocating a row, page, or series of pages to clear the errors. Any deallocated data is no longer accessible or recoverable for the user, and the exact contents of the deallocated data cannot be determined.
Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. The REPAIR_ALLOW_DATA_LOSS option is an emergency “last resort” option recommended for use only if restoring from a backup is not possible.
- Put the database in
MULTI_USER
mode now.
ALTER DATABASE [dbaguides] set multi_user
GO
Let’s check the database state now.
select name, state_desc
from sys.databases
where name='dbaguides'
Voila 🤩 The database is online and accessible now 🔥
Alternatively, you can use the database detach-attach method as well. The sp_attach_single_file_db
command attaches the database to the server by re-building a new log file altogether.
ALTER DATABASE [dbaguides] SET EMERGENCY;
ALTER DATABASE [dbaguides] set multi_user;
EXEC sp_detach_db [dbaguides];
EXEC sp_attach_single_file_db @DBName = [dbaguides], @physname = N'D:\SQL Server\Data\dbaguides_dat.mdf'; --Specify the .mdf file path
However, try to avoid using the database detach-attach method. The
sp_attach_single_file_db
command feature is currently in maintenance mode and it may be removed in a future version of SQL Server. Refer here for details.
Now that the database is recovered, let’s do some RCA 💡
What does the database RECOVERY_PENDING
state signifies?
The database RECOVERY_PENDING
state indicates that the database can’t start the crash recovery process. This could be due to either of the below two points –
SQL Server cannot access the boot page of the primary data file to determine whether a crash recovery is needed.
SQL Server has already determined that a crash recovery is needed, but it couldn’t start the recovery process maybe because the transaction log file is missing or corrupted.
The database RECOVERY_PENDING
state was introduced in SQL Server 2008 release. Before that, during SQL Server 2005 or earlier era, there used to be only one state – SUSPECT
mode.
So, what does the database SUSPECT
mode signifies? 🤔
The database SUSPECT
mode indicates that the database recovery process has been started, but it failed to complete and that the database couldn’t be brought online for user connections. Possible reason for a database to go into SUSPECT
mode is that if the transaction log file becomes inaccessible or damaged due to some reason and preventing a transaction rollback process to complete.
Essentially, the solution to recover database from both the problem – database SUSPECT
mode or RECOVERY_PENDING
state, is same. You can follow the above steps in order to bring the database back to ONLINE
.
And yes, there are a number of 3rd-party database recovery tools also available in the market, which can help you to recover database in a much easier way.
Hope you found this article informative and it has helped you to timely resolve your issue.
Happy Learning 🙏