Fix Transaction Log Corruption During SQL Server Recovery

Quick answer

Set the database to EMERGENCY mode, run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS, then switch to SINGLE_USER and rebuild the log. You'll lose recent transactions but the database comes back online.

Why this happens

This error fires when SQL Server can't read a critical portion of the transaction log file (.ldf) during the automatic recovery phase at startup. Common triggers: a sudden power loss on a busy OLTP server (I've seen this on a financial app after UPS failure), a disk write cache that lied about flushing, or a buggy storage driver. The log file holds all uncommitted transactions and the recovery process needs it to roll forward or back. When a log block is corrupted, SQL Server can't decide what to do — so it screams and stops.

I know this error is infuriating if you just want the database running. The good news: the actual data pages are usually fine. It's the log's metadata that's toast.

Step-by-step fix

Switch to EMERGENCY mode
Open SQL Server Management Studio (SSMS) or sqlcmd. Run this against the corrupted database:
```
ALTER DATABASE [YourDB] SET EMERGENCY;
```
This tells SQL Server it's okay to let you in even though recovery failed. The database appears but is read-only.
Run DBCC CHECKDB with repair
You need to force repair, which rebuilds the log from scratch. This destroys any uncommitted transactions. In SSMS with a new query:
```
ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB('YourDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
```
Yes, allow data loss sounds scary. It's the only way if the log is corrupt. The command scans every page and the leftover log records, then builds a new log file. I've run this on a 2TB warehouse — took 4 hours, but it worked.
Bring the database back online
After repair completes without errors (or with only minor ones), set it to MULTI_USER:
```
ALTER DATABASE [YourDB] SET MULTI_USER;
ALTER DATABASE [YourDB] SET ONLINE;
```
Verify integrity
Run a quick consistency check without repair to make sure everything is clean:
```
DBCC CHECKDB('YourDB') WITH NO_INFOMSGS;
```

Alternative fixes

If the main fix fails — say DBCC throws another corruption error in the data pages — you have harder choices:

Restore from backup. If you have a recent full backup and transaction log backups, restore to a point before the corruption. Use RESTORE DATABASE [YourDB] FROM DISK = 'path' WITH NORECOVERY, then apply logs. This is the safest path.
Use a third-party tool. I've had success with ApexSQL Log and Stellar Repair for MS SQL when DBCC refuses. They extract data from the corrupt log file directly. Not free, but beats data loss.
Export data via bcp. If the database is in EMERGENCY mode but you can't repair, use bcp to dump all tables to CSV, then import into a new database. Painful but reliable.

Prevention tip

This error rarely happens twice if you fix the root cause. Three things I always check after recovering:

Enable instant file initialization — gives SQL Server the permission to allocate space without zeroing out bytes, reducing log write contention.
Use a UPS with proper shutdown integration. A power drop that kills the disk write cache mid-flush is the #1 cause in my experience.
Schedule regular DBCC CHECKDB — weekly on production databases. Catch corruption before it tangles the log.

And if you're on SQL Server 2016 or older, consider upgrading. The 2019+ log manager handles I/O errors more gracefully and will sometimes truncate the bad block instead of dropping dead.