SUSPECT vs. RECOVERY_PENDING

Question: I’ve been working through some disaster recovery scenario testing and sometimes I see databases marked SUSPECT and sometimes RECOVERY_PENDING. Why are there two different database states when in earlier versions there was only SUSPECT?

Answer: From SQL Server 2005 onwards (when the metadata subsystem inside SQL Server was extensively rewritten), the database management portion of the Storage Engine has surfaced a variety of new states in the sys.databases catalog view. The SUSPECT and RECOVERY_PENDING statuses are needed because they describe quite different states of the database.

The RECOVERY_PENDING state means one of two things: either the database could not be opened to determine whether crash recovery needs to be performed, or crash recovery needs to be performed but was unable to start.

The boot page (page 9 in file 1) contains a flag that indicates whether the database was cleanly shut down (i.e. with no uncommitted transactions) or not. If the boot page is inaccessible for some reason, the database cannot be started as SQL Server doesn’t know if crash recovery has to be performed to make the database transactionally (and potentially structurally) consistent.

If the boot page indicates that crash recovery needs to be performed, but something is preventing crash recovery from beginning (e.g. a missing transaction log file, or a damaged log file header), then the database cannot be opened as there’s no other way to make the database transactionally consistent.

The SUSPECT state means that recovery started but was unable to complete. Note that I didn’t say “crash recovery” – instead I just said “recovery”. This is because the act of making the database transactionally consistent could be performed while starting the database (i.e. crash recovery) or is a running transaction needs to be rolled back for some reason.

In the first case, a failed crash recovery will mark the database SUSPECT. In the second case, if a transaction is rolled back and hits some kind of corruption which prevents the rollback completing, the database is then transactionally inconsistent and has to be taken offline immediately, and marked SUSPECT.

In other words, you could think of the SUSPECT state as being that SQL Server knows the database is inconsistent in some way

It’s very important to know the difference between these states as SUSPECT is a more dire situation than RECOVERY_PENDING. In the RECOVERY_PENDING case, you may be able to get crash recovery to proceed by setting the database offline and then online again. If this does not work, or you’ve got a SUSPECT status, restoring from backups may be the best bet, or potentially using EMERGENCY mode to access the inconsistent data if no backups are available.