Database is not online or not in multi user mode

What this check looks for

Every database on the instance whose state_desc is not ONLINE, or whose user_access_desc is not MULTI_USER.

Two things are deliberately left out, because they are normal rather than wrong:

  • Database snapshots, which have a source_database_id and are not independent databases.
  • Databases that are restoring for a reason, meaning a log shipping secondary named in msdb.dbo.log_shipping_monitor_secondary, or a database being seeded into an Availability Group. A secondary sitting in RESTORING is doing its job.

The finding is raised as Urgent for SUSPECT, EMERGENCY, RECOVERY_PENDING and OFFLINE, and as a warning for everything else, because the first four are an outage and the rest are a door left locked.

Why it matters

A database in one of these states is almost always something somebody stopped looking at. Nothing on the instance raises its hand about it again.

  • RECOVERY PENDING means SQL Server could not start recovery, usually because a file is missing or the drive it lives on did not come back after a restart.
  • SUSPECT means recovery started and failed. The database is not usable and the cause is very often corruption or a lost log file.
  • EMERGENCY is a state a person put it in, to read data out of a damaged database. It is a step in a repair, not a place to leave a database.
  • OFFLINE is deliberate, and the question is whether the person who took it offline still works here.
  • SINGLE USER lets exactly one session connect, and blocks everyone else. After a repair this is frequently the state a database is forgotten in, and the one session that gets in is often a monitoring tool rather than a person, which makes the database look simultaneously up and unreachable.
  • RESTRICTED USER does the same thing to everyone outside db_owner, dbcreator and sysadmin.

How to confirm it yourself

SELECT d.[name],
       d.[state_desc],
       d.[user_access_desc],
       d.[recovery_model_desc],
       d.[is_read_only]
  FROM sys.databases AS d WITH (NOLOCK)
 WHERE d.[source_database_id] IS NULL
   AND (    d.[state_desc]       <> 'ONLINE'
         OR d.[user_access_desc] <> 'MULTI_USER' )
 ORDER BY d.[name];

If a row comes back RESTORING, check whether it is a log shipping secondary or an Availability Group database before treating it as a problem:

SELECT [secondary_database]
  FROM msdb.dbo.log_shipping_monitor_secondary WITH (NOLOCK);

How to fix it

The fix depends entirely on the state, and the first move is always to read the SQL Server error log for the entries around the time the database entered it.

State Where to start
SINGLE_USER ALTER DATABASE [name] SET MULTI_USER; Kill the session holding it first if the statement blocks.
RESTRICTED_USER ALTER DATABASE [name] SET MULTI_USER;
OFFLINE ALTER DATABASE [name] SET ONLINE; and find out why it was taken offline.
RECOVERY_PENDING Find the missing or unavailable file. The error log names it. Fix the storage, then set the database online.
SUSPECT Restore from backup. This is the right answer far more often than a repair is. Only if there is no backup does EMERGENCY plus DBCC CHECKDB with a repair option come into it, and that option loses data.
EMERGENCY Somebody put it here on purpose. Finish or abandon the repair rather than leaving it.

Do not run DBCC CHECKDB ... REPAIR_ALLOW_DATA_LOSS as a first response to a suspect database. It does what its name says.

How long it takes

About an hour to investigate and clear the state, assuming a restore is not needed. A restore takes as long as the database is large.

Report Why you would go there
Availability Groups Whether a restoring database is a replica that is doing its job.
Log Shipping The same question for a log shipping secondary.
Recovery Exposure What a restore of this database would actually cost you.
Suspect Pages Whether corruption is already recorded for this database.
Error Log The entries around the moment the database changed state.
Check
Availability Group database is not healthy A replica that is online but not protecting anything.
DBCC CHECKDB Corruption Errors Found The usual reason a database goes suspect.
Log shipping secondary is behind its threshold A secondary that is restoring, but too slowly.

Frequently asked questions

A log shipping secondary is listed anyway. Why? Because it is not in RESTORING. A secondary in STANDBY shows as online and read only, and one that has gone RECOVERY_PENDING has stopped being a secondary at all.

Why is a database snapshot not reported? A snapshot has a source_database_id, and the check excludes those. A snapshot is not an independent database and its state follows its source.

The database says ONLINE but nobody can connect. Look at the user_access_desc column rather than the state. SINGLE_USER with a session already connected looks exactly like this.

Can this check be safely ignored on a development instance? Nothing here is a false positive, but a developer deliberately leaving a database offline is a different problem from a production database in RECOVERY_PENDING. The state tells you which one you have.