How to Fix a Suspect or Recovery Pending Database in SQL Server?

Updated: Aug 12, 2021

One of the scariest moments for a DBA is to see a database in the "Suspect" mode or in the "Recovery Pending" mode...


First, let's understand the difference between these two database modes...


The "Recovery Pending" mode was added in SQL Server 2008, so before, there was only the "Suspect" mode. Generally speaking, when a database was "Suspect" in SQL Server 2005 or earlier version, it meant that the database couldn't be brought online, and thus, it wasn't available for user connections.


Starting with SQL Server 2008, we have two modes - "Suspect" and "Recovery Pending". The "Recovery Pending" mode means that the database can't start the crash recovery process. This is either because SQL Server cannot access the boot page in the primary data file in order to determine whether a crash recovery is needed, or it determined that crash recovery is needed, but it couldn't start the recovery process (for example, because the transaction log file is missing or corrupted).


The "Suspect" mode means that the database started the recovery process, but it failed to complete. For example, this can happen if the transaction log file becomes inaccessible during a transaction rollback operation.


Although it's important to understand the difference between the two modes, usually, the steps to fix the problem are the same. In order to keep it simple, I will list here the actions you should take regardless of the database mode ("Recovery Pending" or "Suspect").


The "Recovery Pending" mode means that the database can't start the crash recovery process

So what do you do when (sh)it happens?


1. Don't panic! I know it's easier said than done, but believe me - this is one of the most important tips I can give you. Because you might do something that will only make things worse, for example, when a database is in the "Suspect" mode, you still might be able to access the database and extract data from it. But if you try to restart SQL Server (because you're under pressure), then you might not be able to access the database anymore. So before you do anything - breathe, drink a glass of water, and then start planning your actions. OK? Are you breathing? Good. Then you can now continue to step #2...




2. Take a backup! Yes, I know you can't really perform a SQL Server backup at this point. But you should try to take some sort of backup of the database files as soon as you can. Sometimes, depending on the state of the database and the database files, SQL Server might not be holding any locks on the files. In this case, copy all the database files to a different location, preferably a separate storage. If this is not possible, then take a storage snapshot or use whatever 3rd-party tool you like, which can take a copy of the database files. Very important - make sure that the tool you are using only tries to read the files and doesn't try to change them in any way

3. Restore from backups. Hopefully, you should have backups available, whic