Use this when a database is not ONLINE. The state tells you which path to follow.

Step 1: Find databases that are not online

SELECT name, state_desc, user_access_desc, recovery_model_desc
FROM sys.databases
WHERE state_desc <> N'ONLINE';

Step 2: Read the error log for that database

EXEC sp_readerrorlog 0, 1, N'YourDB';

The parameters are the log number (0 = current), the log type (1 = SQL Server) and a search string. The messages around the failure or the last start-up usually name the cause.

RECOVERING: let it finish

Crash recovery is running (analysis, redo, then undo). Long-running transactions and a high VLF count make it slow. Track progress in the error log:

EXEC sp_readerrorlog 0, 1, N'Recovery of database';

Do not restart SQL Server to speed it up. Recovery starts again from the beginning.

RECOVERY_PENDING: fix the cause, then bring it online

SQL Server could not start recovery, usually because a data or log file is missing or inaccessible (drive offline, permissions changed, path changed) or there is no disk space. Fix the cause, then:

ALTER DATABASE [YourDB] SET ONLINE;

SUSPECT: restore from backup

Recovery started and failed, usually because of corruption or an I/O error (see Corruption Errors 823, 824 and 825). The preferred fix is to restore from a good backup (see Backup and Restore with T-SQL).

Emergency repair: last resort only (NEEDS APPROVAL)

Use this only when there is no usable backup. REPAIR_ALLOW_DATA_LOSS can delete data and leaves the database transactionally inconsistent. Rollback: none, so first take the database offline and copy its data and log files somewhere safe.

ALTER DATABASE [YourDB] SET EMERGENCY;
ALTER DATABASE [YourDB] SET SINGLE_USER;
DBCC CHECKDB (N'YourDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
ALTER DATABASE [YourDB] SET MULTI_USER;

A database in an availability group cannot be repaired in place. Remove it from the AG first, or restore it from a healthy replica.

From my SQL Server DBA handbook.