To shrink a SQL Server LDF file, back up the transaction log first, then run DBCC SHRINKFILE (LogFileName, SizeInMB). In simple recovery a checkpoint clears the log for you, in full recovery a log backup does. Never delete the LDF by hand, the database will not start without it. If your log is huge because there is no backup routine, shrinking is a patch, and the real fix is right sizing the log and taking regular log backups.
A SQL Server LDF file, the transaction log, grows over time and can fill a drive if nothing keeps it in check. The usual reason someone lands here is a log that has swollen to tens of gigabytes and a disk running out of space. Shrinking it is the quick relief, but doing it safely means understanding two operations people mix up and one recovery setting that controls both. This page covers how to shrink the log the right way, why it can refuse, and why deleting the file is never the answer. To read what is actually inside the log rather than manage its size, see reading a SQL Server transaction log, and the LDF file extension page explains what the file is.
Truncate and Shrink Do Different Jobs
These two words get used as if they mean the same thing, and the confusion is behind most failed attempts to reclaim space. Truncate frees space inside the log by marking old records reusable, but it does not make the file any smaller on disk. Shrink is the operation that actually reduces the physical size of the LDF file. You need both in most cases, truncate to make the space inside reusable, then shrink to hand that freed space back to the drive.
The order is the part people miss. Running a shrink on a log that has not been truncated returns almost nothing, because there is no free space inside to hand back. That is the top reason a shrink appears to do nothing, and it points straight at the recovery model.
Why the Recovery Model Decides Everything
Whether the log truncates on its own comes down to the database recovery model, and this one setting explains most runaway log files. In the simple recovery model, SQL Server truncates the log automatically at each checkpoint, so the space inside keeps getting reused and the file rarely grows out of control. Under full or bulk logged recovery, the log is held until a transaction log backup runs, and only that backup frees the space.
Here is the trap that fills drives. A full database backup does not truncate the log. Plenty of databases sit in full recovery with nightly full backups and no log backups at all, and their logs grow without limit because nothing is ever clearing them. If that describes yours, the log backup is the missing piece, not the shrink.
When the log finally fills the disk, SQL Server raises Error 9002, the transaction log for the database is full. That is the moment most people go looking to shrink, but the message is really telling you the log has nowhere left to grow and its space is not being reused. The fix starts with finding out why the space is stuck, not with the shrink itself.
Shrink an LDF File with T-SQL
The command that shrinks the log is DBCC SHRINKFILE, and it takes the logical name of the log file plus the size you want in megabytes. First find the logical name, since it is not the file name on disk.
BACKUP LOG [YourDatabase] TO DISK = N’C:\Backup\YourDatabase_log.trn’;
DBCC SHRINKFILE (YourDatabase_log, 512);
In full recovery the safe sequence is the one above, back up the log to free the space inside, then shrink to a sensible target size. If point in time recovery does not matter for this database, the alternative is to switch it to simple recovery, run a checkpoint to clear the log at once, shrink, then switch back and take a fresh full backup to restart the backup chain. Pick a target size that fits normal activity rather than the smallest possible number, since a log squeezed too small just grows straight back.
Shrink an LDF File in SSMS
The graphical route does the same thing without typing. In SQL Server Management Studio, walk these steps.
- Right click the database, choose Tasks, then Shrink, then Files.
- Change the File Type to Log so you are working on the LDF, not the data file.
- Choose to release unused space, or set a target size to shrink to.
- Click OK to run the shrink.
The wizard shrinks only the free space that already exists in the log, exactly as the command does, so a log that has not been truncated will barely move here either. If the SSMS shrink returns almost nothing, that is the recovery model and the missing log backup talking, not a fault in the wizard. Back up the log first, then the shrink has room to work.
When the LDF File Refuses to Shrink
A log can refuse to shrink even after a backup, and SQL Server can tell you exactly why. Something is holding the log open, and one query names the reason.
The log_reuse_wait_desc column shows what is blocking the space from being reused. A value of LOG_BACKUP means you still owe a log backup, ACTIVE_TRANSACTION means a long running transaction is holding the log open, and a value naming replication or an availability group means a high availability feature is keeping records until they ship elsewhere. When it points to an open transaction, DBCC OPENTRAN names the oldest one so you can commit it, roll it back or wait for it to finish. Clear the reason it names and the shrink then works. There is also a floor on how small the log can go. It shrinks only to the boundary of an internal virtual log file and cannot drop below the size of one, which is why a shrink can stop short of the number you asked for.
Why You Cannot Just Delete the LDF
When a log file is huge and a drive is full, the tempting shortcut is to stop the service and delete the LDF. Do not. The transaction log is not an optional extra, it is part of the database, and SQL Server will not start or attach the database without it. Delete the LDF and the database drops into a suspect state and will not open, turning a space problem into a recovery one.
The safe way to shrink is never destructive. Truncate frees the space, shrink hands it back and the database stays healthy throughout. There is simply no version of the delete shortcut that ends well.
Shrinking Does Not Belong on a Schedule
Shrinking a log is a one off fix for an emergency, not a job to schedule. Every shrink causes the log to fragment internally, and worse, a log that is regularly shrunk just grows straight back to the size it needed, so you pay the cost of the regrowth over and over. A shrink that runs on a schedule is a sign the log is sized wrong, not a maintenance win.
The one time a shrink earns its place is right after a one off event that bloated the log, a giant data load or a long maintenance operation that will not repeat. In that narrow case, shrinking the one time spike back down makes sense. As anything recurring, it is a symptom being treated instead of a cause.
Fixing the Log Growth for Good
The lasting fix is not a bigger habit of shrinking, it is stopping the log from growing out of control in the first place. That comes down to two things. Set the recovery model to match how much recovery the database actually needs, since a development or reporting database rarely needs full recovery and its overhead. And if it does need full recovery, schedule regular transaction log backups, which is the piece that keeps the log clearing itself.
Sizing the log sensibly from the start helps too. A log set to a realistic size for the workload, with a fixed growth increment rather than tiny automatic jumps, avoids both the runaway growth and the fragmentation that constant shrinking causes. Get the recovery model and the backup schedule right and the log stops being a problem you have to keep shrinking.
Frequently Asked Questions
How do I Shrink a SQL Server LDF File?
Back up the transaction log to free the space inside it, then run DBCC SHRINKFILE with the log’s logical name and a target size in megabytes. In simple recovery a checkpoint clears the log instead of a backup. Find the logical name with SELECT name FROM sys.database_files WHERE type_desc = ‘LOG’. Shrink to a size that fits normal activity, not the smallest possible.
Can I Delete the LDF File to Free Space?
No. The LDF is the transaction log and part of the database, and SQL Server will not start or attach the database without it. Deleting it leaves a database that refuses to open, turning a disk space issue into a recovery one. To reduce the size safely, back up the log and shrink the file with DBCC SHRINKFILE instead.
Why Does my LDF File Refuse to Shrink?
Because something is holding the log open, so the space inside it cannot be reused yet. Run SELECT name, log_reuse_wait_desc FROM sys.databases to see the reason, commonly a pending log backup, a long running transaction or a high availability feature. Clear that reason, then shrink. The log also cannot go below the size of one internal virtual log file.
What is the Difference Between Truncate and Shrink?
Truncate frees space inside the log by marking old records reusable, but it leaves the file the same size on disk. Shrink reduces the physical size of the LDF file with DBCC SHRINKFILE. You need both, truncate to free the space then shrink to return it to the drive. Truncate depends on the recovery model, shrink is a physical file operation.
Does a Full Backup Truncate the Transaction Log?
No. A full database backup does not truncate the log. Only a transaction log backup, in the full or bulk logged recovery model, clears the log records and frees the space. This is why databases in full recovery with only nightly full backups grow their logs without limit, the log is never being cleared.
How do I Stop the LDF From Growing so Large?
Match the recovery model to the database’s real recovery needs, since simple recovery clears the log automatically and suits development or reporting databases. If full recovery is required, schedule regular transaction log backups, which keep the log clearing itself. Sizing the log realistically with a fixed growth increment avoids the constant shrinking that only causes fragmentation.