Fix corrupted SQL transaction log — 3 real fixes

Step 1 — The 30-second fix: Check for simple causes

Before you panic, some "corrupted" logs aren't really corrupted. I've had clients call me at 2 AM thinking their log is toast, and it was just full.

First, try to restart SQL Server Service. Run this from command prompt as admin:

net stop MSSQLSERVER
net start MSSQLSERVER

If the service starts, run DBCC CHECKDB to see if it reports real corruption:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

If you see error 9002 or 824, move on. If it's just full, you can truncate the log:

BACKUP LOG YourDatabaseName TO DISK = 'NUL';
DBCC SHRINKFILE('YourLogFileName', 100);

That's not a fix for the corruption — it just clears space. If the log is truly corrupted, BACKUP LOG will fail with error 9002 or 823.

Step 2 — The moderate fix (5 minutes): Set database to emergency mode and rebuild log

This works if the data files are intact but the log file is hosed. I did this last month for a client's accounting database on SQL Server 2016 — they had a power outage mid-transaction and the log refused to come online.

Set database to emergency mode (single user):

ALTER DATABASE YourDatabaseName SET EMERGENCY;
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Then run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. I know the name sounds scary, and yes, you can lose some data — but it's better than a dead database. Here's the command:

DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

If it completes, set the database back to multi-user and normal mode:

ALTER DATABASE YourDatabaseName SET MULTI_USER;
ALTER DATABASE YourDatabaseName SET ONLINE;

Now run a full backup immediately. You've fixed the log, but the database might have inconsistencies. Also run:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS;

If you get more errors, you need the advanced fix.

Step 3 — The advanced fix (15+ minutes): Rebuild log file from scratch

This is when REPAIR_ALLOW_DATA_LOSS didn't work, or the database won't even go into emergency mode. I've had to do this for a client's ERP database on SQL Server 2019 where the log file had a physical bad sector.

First, detach the database but keep the data files:

EXEC sp_detach_db 'YourDatabaseName', 'true';

Now rename or delete the corrupted .ldf file. Don't delete it if you're not sure — just move it to a backup folder.

Then attach the database without the log file. SQL Server will create a new empty log:

CREATE DATABASE YourDatabaseName ON 
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\YourDatabaseName.mdf')
FOR ATTACH_REBUILD_LOG;

If the data file has issues too, you might get error 5172. In that case, you can try attaching with the old log file still there but marked as unusable. But honestly, if the data is hosed, you need to restore from backup.

After the attach, run DBCC CHECKDB again to confirm the database is clean:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS;

If you see any corruption in the data, you'll need to restore from a clean backup. I always remind clients: no backup, no mercy. This fix gets you a working log, but it doesn't fix data already lost.

Quick tip: If you're on SQL Server Standard or Express, you can't use online restore or some fancy options. The above works on all editions. I've used this on Express edition for a client's small POS system.

When to stop and restore from backup

If none of these work, you've got a serious problem. The data file itself may be corrupted, or the storage subsystem has issues. I had a client whose RAID controller was failing — replacing the log file didn't help because writes were corrupting both files. In that case:

Restore from the most recent full backup
Apply all log backups
Run tail-log backup if possible

If you don't have a backup, you're in a bad spot. Call a data recovery company or try third-party tools like ApexSQL Log or Stellar Repair for SQL. I've seen those work for minor corruption, but they're not cheap.

Preventing this from happening again

Here's what I tell every client after fixing their log:

Set your recovery model to Simple if you don't need point-in-time recovery — logs won't grow as big
Schedule regular log backups every 15 minutes if you use Full recovery model
Monitor disk space for the log file drive — 90% full is a red flag
Use SQL Agent alerts for error 9002

One last thing: if your database is in Simple recovery mode and you still get log corruption, check your disk. I've replaced two dying SSDs this year alone that caused log corruption. Your storage is the real weak link.