Error 9001: The Log for the Database Is Not Available After a Crash

Fix it now

The engine cannot get at the transaction log for a database. Microsoft’s own topic names four causes: storage that failed or went away, a physically damaged log file, a Transparent Data Encryption or external key store failure, and a log that is simply full. The route that keeps your committed work is a restore, not a rebuild.

SELECT name, state_desc FROM sys.databases WHERE name = N'MyDb';
SELECT name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'MyDb');
EXEC sys.sp_readerrorlog 0, 1, N'MyDb';

1. Read the error log entries around the failure and note whether the message is about opening the file or about processing its contents. 9001 rarely arrives alone: 9002, 3313, 3314, 17204, 17053 or 823 usually sit beside it and carry the real diagnosis.
2. If a 9002 came first, this is a full log rather than a missing one. Free the space, find out why it was not released, and this article’s harder paths do not apply.
3. If the volume was briefly offline and is back, try ALTER DATABASE [MyDb] SET ONLINE; and watch the error log.
4. If the database uses TDE with an external key store, check that the key management module is up before touching the files.
5. If the file is missing, damaged or mismatched, restore from your last backup and roll the log chain forward.

Do not detach the database, and do not rebuild the log, while a restore is still an option. Detaching a database that will not recover can leave you without a route back.

If the database comes online and CHECKDB is clean, you are done. If it will not, the next section explains which phase of recovery is failing.

Why it happens

When a database comes online the engine runs recovery in three phases. Analysis reads the log forward from the last checkpoint to work out what was in flight. Redo replays committed changes that had not reached the data file. Undo rolls back transactions that were still open. All three read the log, which is why a log problem stops a database from starting even when the data file is perfectly healthy.

9001 itself is a statement about the outcome rather than the cause. Its message says the log is not available and to check the operating system error log for related messages, and Microsoft’s own topic is explicit that the error shows the end result and does not explain the underlying reason. It lists four: the log file sits on failed or unavailable storage, the file is physically damaged, the file cannot be accessed because of a Transparent Data Encryption failure, or the log is full because of a large transaction, low disk space or a file size limit.

That TDE cause catches people out. If the database is encrypted with a key held in an external Extensible Key Management module or hardware security module, and that module is unavailable or misbehaving, the engine cannot read the log and reports it as unavailable. No amount of storage investigation finds that, and the remedy is with the key management vendor rather than with the disks.

9003 and 9004 are the more serious neighbours. 9003 says a log scan number passed to log scan is not valid, and it names two causes in this order: this error may indicate data corruption, or that the log file does not match the data file. A mismatched pair after an attach or a partial restore is the common one, but corruption is named first and should not be ruled out. The message adds a third case people forget: if the error occurred during replication, re-create the publication. 9004 is broader still – an error occurred while processing the log – and its own text says restore from backup if possible, and rebuild the log only if a backup is not available.

Full reference

Where the fault is, by what you see

The recovery phases, and which code belongs to which

3456 is worth reading closely when it appears, because it prints the log record LSN, the transaction ID, the page and the page’s own LSN. That is enough to tell whether one page is the problem or the whole log is. Microsoft also documents a consequence that changes the urgency: if a 3456 occurs during redo in tempdb, the SQL Server instance shuts down.

What rebuilding the log actually costs

Rebuilding or discarding a transaction log throws away every transaction that had not yet been written to the data file. The database that results is transactionally inconsistent: rows can be missing, half-applied, or in violation of constraints that the engine will not re-check for you. It is a salvage operation, not a repair, and it is never the first thing to try.

Microsoft’s own ordering in the 9004 message says the same thing more gently: restore from backup if possible, and if a backup is not available it might be necessary to rebuild the log. The second clause exists because sometimes there is no alternative, not because it is a supported way to fix a database you still have backups for.

Getting the state right before you act

• RECOVERY_PENDING means recovery could not start, usually because a file is missing or inaccessible. Making the file available is often the whole fix.
• SUSPECT means recovery started and failed. That more often needs a restore.
• ONLINE with errors in the log means recovery completed and something else is wrong. Run CHECKDB.
• Read the state before acting: SELECT name, state_desc, is_read_only FROM sys.databases WHERE name = N'MyDb';
• Do not put a database into EMERGENCY mode to find out what state it is in. That is a one-way step towards repair.

Preventing the repeat

1. Make the SQL Server service depend on its storage service, so it does not start before iSCSI or SAN volumes are presented.
2. Exclude database file extensions from real-time antivirus scanning, and check quarantine when a file goes missing.
3. Monitor free space on the data and log volumes, and alert before recovery needs space it does not have.
4. Keep the external key management module patched and monitored if the database uses TDE with one.
5. Test that your backups restore. Every route out of a damaged log that keeps your data goes through a backup you took earlier.

Every code this article covers

Confirm the fix worked

1. Confirm the database is online: SELECT name, state_desc FROM sys.databases WHERE name = N'MyDb';
2. Read the error log and confirm recovery reported analysis, redo and undo completing for that database.
3. Run DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS, ALL_ERRORMSGS; before returning it to users.
4. Take a fresh full backup immediately, and confirm RESTORE VERIFYONLY passes against it.
5. Confirm the service dependency on storage is in place, so a reboot does not repeat the failure.

Questions people ask about this