Why is my LDF file taking up so much space, and how can I free up that space?

Question

Why is my database log file taking to high space? It's taking up almost 30GB of my HDD. Even after deleting 1,000,000 records, it's not freeing up any space.

1. Why is the log file taking this much space (30gb)?
2. How can I free up the space?

Answer 1

1. Why is the log file taking this much space (30gb)?

It was because the Autogrowth / Maxsize was set to 200,000 MB.

2. How can I free up the space?

As described here I used the following command and the file is now less than 200 MB.

    
ALTER DATABASE myDatabaseName
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (myDatabaseName_log, 1)
GO
ALTER DATABASE myDatabaseName_log
SET RECOVERY FULL

I have also set Autogrowth/Maxsize in the database properties to 1000 in the Limited option (See the image below).

The link describes more, so I recommend referring it for detailed description and other options.

Answer 2

1.Why is the log file taking this much space (30gb)?

• Because of your recovery mode is not set to SIMPLE and the LDF file has grown eventually to such size
• Or because there was a large one-time DML operation
• Or because of other reasons, as noted by sepupic in another answer

2.how can I free up the space?

• IF recovery is other than SIMPLE:

• Firstly backup transaction log file

• Perform a shrink, like DBCC SHRINKFILE(2,256)

• IF recovery is SIMPLE:

• Just shrink it to desired size, like DBCC SHRINKFILE(2,256)

If the database log still did not reduce to a target size, then the exact reason to be checked, by using the code snippet from sepupic's answer

---

Some members still give and advice to physically remove LDF files. I highly suggest that you not do this. From a related post by Aaron Bertrand:

Some things you don't want to do:

Detach the database, delete the log file, and re-attach. I can't emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc.