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 in order 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 a 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 checkout the reason behind it, once 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
Emergencymode first. It puts the database in
READ_ONLYmode, disable logging and become accessible to the
ALTER DATABASE [dbaguides] SET EMERGENCY; GO
- Put the db in
ALTER DATABASE [dbaguides] set single_user GO
DBCC CHECKDBin order 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
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 basically attaches the database to the server with 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_dbcommand 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?
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 in order 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 of the transaction log file is missing or corrupted.
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 –
So, what does the database
SUSPECT mode signifies?
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
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 !!