SQL Server – Clear Log File

The 2010 recipe floating around (and the one I originally posted) for an out-of-control transaction log: switch the database from FULL to SIMPLE recovery, shrink the log file, switch back to FULL. It works, and it’s a ten-second fix when a dev log has ballooned to 40 GB. It’s also the version of the story that leaves people confused about why their log grew in the first place — and quietly breaks log shipping, mirroring, and point-in-time recovery until the next full backup. Here’s the full picture.

Why the log fills up in the first place

Under FULL recovery, the transaction log can only be truncated after a log backup. If nothing is taking log backups — a database restored from production onto a dev box, or a database someone set to FULL without ever scheduling log backups — the log grows until it eats the disk. The transaction log management guide is the authoritative walkthrough of truncation and reuse.

Diagnose before shrinking. If log_reuse_wait_desc says anything other than NOTHING, shrinking won’t reclaim anything anyway:

SELECT [name], recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID();

-- How full is the log, and how many VLFs does it have?
SELECT db.name,
       mf.name AS logical_name,
       mf.size / 128.0 AS size_mb,
       (SELECT COUNT(*) FROM sys.dm_db_log_info(mf.database_id)) AS vlf_count
FROM sys.databases AS db
JOIN sys.master_files AS mf
    ON mf.database_id = db.database_id AND mf.type_desc = 'LOG'
WHERE db.name = DB_NAME();

The honest 2010 recipe (still the fastest fix for dev)

1. Switch the recovery model from FULL to SIMPLE:

ALTER DATABASE [MyDb] SET RECOVERY SIMPLE;

2. Shrink only the log file — never the data file. In SSMS: right-click the database → Tasks → Shrink → Files, file type “Log”. In T-SQL, with the log’s logical name from sys.database_files:

USE [MyDb];
SELECT [name], size/128.0 AS size_mb FROM sys.database_files WHERE type_desc = 'LOG';

DBCC SHRINKFILE (MyDb_log, 1024);  -- target size in MB

3. Switch back to FULL and take a full (or differential) backup immediately. The database stays in pseudo-simple mode — no log truncation for point-in-time recovery — until the first full/differential backup after the switch back establishes a base:

ALTER DATABASE [MyDb] SET RECOVERY FULL;
BACKUP DATABASE [MyDb] TO DISK = 'D:\Backups\MyDb_full.bak' WITH COMPRESSION;

What the simple recipe costs you

• It breaks the log chain. Every log backup taken between the last one before the switch and the first one after switching back is unusable for point-in-time recovery. On production systems with log shipping, mirroring, or AGs, this is a real incident, not a cleanup step. Microsoft’s recovery models documentation covers the switching rules; the log backup docs cover the chain itself.
• Shrink-and-regrow fragments the log into thousands of tiny VLFs, which slows crash recovery, log backups, and AG catch-up. Don’t shrink to 10 MB “because you can”: pick a size that covers your normal peak log growth (often a few GB) and let it stay there.
• It treats the symptom. If the log ballooned because nothing was taking log backups, it will balloon again. The real fix on a server where point-in-time recovery doesn’t matter: leave the database in SIMPLE recovery permanently and take regular full/differential backups. On a system where it does matter: schedule log backups every few minutes and monitor them.

Bottom line: for a dev or sandbox database, the 2010 three-step is fine and I still use it. For anything with a backup SLA, set the recovery model deliberately, schedule log backups, and leave SHRINKFILE out of routine maintenance entirely.