Database Marked SUSPECT (Error 926): EMERGENCY, CHECKDB, or the Backup

A database came back from a restart marked SUSPECT, nobody can connect to it, and the first page of search results is selling you recovery software. This is the order a DBA works in instead.

Everything below is a real run. A 25,000 row database was taken down mid transaction, its data file was damaged on disk, and the instance was restarted. The reproduction ran on SQL Server 2022 in a Linux container, so the file paths in the captures are Linux paths. The messages, the state names and the commands are identical on Windows.

Confirm It Really Is SUSPECT

That message is the only thing a connection will give you, and it names the wrong place to look for the cause. Ask the instance for the state instead, from master, because you cannot enter the database itself:

-- Run this in master. It works whatever state the database is in.
SELECT name, state_desc, recovery_model_desc, page_verify_option_desc
FROM   sys.databases
WHERE  name = N'YourDatabase';

name              state_desc  recovery_model_desc  page_verify_option_desc
----------------  ----------  -------------------  -----------------------
Lab1001b_Suspect  SUSPECT     FULL                 CHECKSUM

Two columns there decide what the rest of your night looks like. recovery_model_desc of FULL means a tail of the log may be takeable, which is the difference between losing nothing and losing everything since the last full backup. CHECKSUM page verification means SQL Server will tell you honestly which pages are damaged rather than reading rubbish and carrying on. If that column says NONE, you are going to have to prove the data is right by other means afterwards.

Read the Error Log: 824, Then 3313, Then 3414

3414 is the line that puts a database into SUSPECT, and on its own it tells you nothing. The lines above it do. Read the log file on disk, or EXEC xp_readerrorlog 0, 1, N'YourDatabase' if you are still connected to the instance. This is the whole failure from the lab run, with the date column trimmed:

15:34:10.96 spid55  Starting up database 'Lab1001b_Suspect'.
15:34:11.42 spid55  Parallel redo is started for database 'Lab1001b_Suspect' with worker pool size [4].
15:34:13.29 spid9s  Error: 824, Severity: 24, State: 2.
15:34:13.29 spid9s  SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x00000000; actual signature: 0xf77736d8). It occurred during a read of page (1:336) in database ID 5 at offset 0x000000002a0000 in file '/var/opt/mssql/data/Lab1001b_Suspect.mdf'.
15:34:13.32 spid9s  Error: 3313, Severity: 21, State: 5.
15:34:13.32 spid9s  During redoing of a logged operation in database 'Lab1001b_Suspect' (page (1:336) if any), an error occurred at log record ID (42:9432:2).
15:34:13.35 spid55  Parallel redo is shutdown for database 'Lab1001b_Suspect' with worker pool size [4].
15:34:13.38 spid55  Error: 3414, Severity: 21, State: 4.
15:34:13.38 spid55  An error occurred during recovery, preventing the database 'Lab1001b_Suspect' (5:0) from restarting.

Read it bottom up and it is a sentence. Recovery could not restart the database (3414) because redo failed (3313) on a specific page, and redo failed because that page did not survive the storage (824). The page number in the 3313 line and the page number in the 824 line are the same, 1:336, which is how you know the corruption and the recovery failure are the same event rather than two problems. Microsoft documents the parent message at MSSQLSERVER_926 on MS Docs, and the I/O error itself at MSSQLSERVER_824.

If your 3414 has no 824 above it, the cause is somewhere else and the pages are probably fine. A full disk, a missing file and an out of memory condition all end at 3414 too, and all three are fixed by fixing the thing the log names, not by repairing anything. The deep dive on the I/O family is SQL Server I/O Errors 823 and 824.

SUSPECT, RECOVERY_PENDING and RECOVERING Are Three Different Problems

All three refuse your connection, and people use the word “suspect” for all three. They are not the same incident and they do not have the same fix. Match the message you actually got:

The 945 case is worth knowing by sight because it is far more common than 926 and it looks much worse than it is. In the same lab, before the data file was damaged, the log file was simply made unreadable to the service account. The database came up RECOVERY_PENDING, USE returned Msg 945, Level 14, State 2, and the error log said FCB::Open failed ... OS error: 5(Access is denied.) followed by Error: 5120. Nothing was corrupt. The file permission was wrong. Fix the file, set the database online, and it comes straight back.

MS Docs lists every state and what each one allows under Database States.

The Order to Work In

The pages selling you repair tools put repair first because repair is what they sell. A production DBA puts it last, and the reason is arithmetic rather than caution. Repair deallocates whatever it cannot fix, and you find out what you lost afterwards. A restore puts back a state you already know was consistent.

1. Find the backups before you touch the database. The most recent full, and every log backup since it. If the recovery model is FULL and the log file itself is readable, you can usually also take a tail of the log, which is the difference between losing an hour and losing nothing.
2. Do not detach it. A detached SUSPECT database often will not attach again, and you have then thrown away the one copy you had. Nothing on this page needs a detach.
3. Do not restart the service “to see if it clears”. Recovery will run again, fail again, and write the same lines. It costs you the time and tells you nothing new.
4. Copy the data and log files somewhere else while the database is offline, if you have the disk. Every route below is then reversible.
5. Only then choose: restore if you have a usable backup, EMERGENCY and repair if you genuinely do not.

EMERGENCY Mode, and What It Actually Buys You

EMERGENCY is the state that lets a sysadmin into a database SQL Server has refused to start. It is a diagnostic door, not a fix, and it is the only way to look at the contents before you decide between restore and repair.

-- From master. You must be sysadmin.
ALTER DATABASE YourDatabase SET EMERGENCY;

SELECT name, state_desc FROM sys.databases WHERE name = N'YourDatabase';

name              state_desc
----------------  ----------
Lab1001b_Suspect  EMERGENCY

Now you can read. How much you can read depends on where the damage landed. In this run a plain SELECT COUNT(*) over the damaged table came back with Msg 601, Level 12, State 3, Could not continue scan with NOLOCK due to data movement, and a full DBCC CHECKDB stopped on the first bad page with another 824. That is a useful answer, not a failed step: it tells you the damage is in the table you care about and not in some index you could rebuild. If the reads succeed, copy what you can out to another database now, before anything else touches this one.

REPAIR_ALLOW_DATA_LOSS, and the Run Where It Refused to Start

The documented sequence is EMERGENCY, then single user, then repair. It is in DBCC CHECKDB on MS Docs and it is the sequence every other page quotes:

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Here is what it returned on a database that was genuinely SUSPECT, in EMERGENCY, in single user mode, exactly as instructed:

Msg 7929, Level 16, State 1, Line 1
Check statement aborted.  Database contains deferred transactions.
Errors occurred during recovery while rolling back a transaction. The transaction was
deferred. Restore the bad page or file, and re-run recovery.

Repair never ran. The instance was taken down with a transaction in flight, that transaction could not be rolled back because the pages it touched are the damaged ones, and CHECKDB will not repair a database in that condition. Running it a second time produced 926, another 824 and another 3414, which is the same answer in a louder voice. A page level restore was not available either: Msg 4309, The state of file "Lab1001b_Suspect" prevents restoring individual pages. Only a file restore is currently possible.

That is the part the recovery software pages do not mention. Repair is conditional, and the condition it most often fails is the one a crashed production instance is most likely to be in. Even when it does run, it fixes consistency by deleting, and what it deletes is rows. If your CHECKDB does run and reports errors, the page that covers reading its output is DBCC CHECKDB Found Corruption.

The Restore Route, Including the Tail of the Log

The move most people miss is the first one. When only the data file is damaged, the log file is often perfectly readable, and a log backup taken from the suspect database with NO_TRUNCATE captures everything up to the moment it died. That is what turns “restore from last night” into “lose nothing”.

-- 1. The tail. NO_TRUNCATE lets this run against a damaged database.
BACKUP LOG YourDatabase TO DISK = 'D:\Recover\tail.trn' WITH INIT, NO_TRUNCATE;

-- 2. The last full, leaving the database able to take more log.
RESTORE DATABASE YourDatabase FROM DISK = 'D:\Backup\full.bak' WITH REPLACE, NORECOVERY;

-- 3. Every log backup since, in order, then the tail last.
RESTORE LOG YourDatabase FROM DISK = 'D:\Recover\tail.trn' WITH RECOVERY;

The lab database held 20,000 rows at the time of the full backup and 25,000 rows when the instance was killed. This is what the three statements returned, and what was in the table afterwards:

BACKUP LOG successfully processed 643 pages in 0.288 seconds (17.428 MB/sec).
RESTORE DATABASE successfully processed 634 pages in 0.342 seconds (14.471 MB/sec).
RESTORE LOG successfully processed 643 pages in 0.099 seconds (50.702 MB/sec).

rows_recovered
--------------
         25000

CHECKDB found 0 allocation errors and 0 consistency errors in database 'Lab1001b_Suspect'.

Every row back, and a clean consistency check, on the same database where repair would not start. If the tail backup fails because the log file is the damaged one, you restore the full plus whatever log backups you hold and you lose the window after the last of them, which is still a known quantity rather than a guess. Check that the chain you are about to rely on is actually intact with Get Backup Chain Integrity before you need it, not during.

Why the Same Damage Says RECOVERY_PENDING on Some Instances

This one caught me out in the lab and it is worth knowing before you go looking for a 926 you are never going to get. The identical crash and the identical file damage were run twice, once on Developer edition and once on Express. Express marked the database SUSPECT. Developer did not:

Errors occurred during recovery while rolling back a transaction. The transaction was
deferred. Restore the bad page or file, and re-run recovery.
Recovery completed for database Lab1001b_Suspect (database ID 5) in 183 second(s)
(analysis 228 ms, redo 181261 ms, undo 193 ms ...)

Developer edition carries the Enterprise feature set, and Enterprise can set aside a transaction it cannot roll back rather than failing recovery. The database landed in RECOVERY_PENDING with 191 rows in msdb.dbo.suspect_pages instead of SUSPECT. The practical consequence is that on Enterprise and Developer you may get 945 where a Standard or Express instance would have given you 926, for exactly the same corruption, and the deferred transaction is then the thing blocking repair. MS Docs covers the mechanism under Deferred Transactions. Either way the answer is the same, and it is the one in the restore section.

Common Questions