Truncating a Log File in SQL Server

SQL Server always keeps a log of every activity performed in the Database in a Log file named as DatabaseName_Log.LDF. Over a period of time if the number of operations increased in the database, this Log file will tend to get really heavy and can grow many times in multiples of the actual database size. Unless until you need to have the whole history of what happened in the database from the beginning of creating it, you can seriously consider truncating this Log file to save some essential space.

Though SQL Server Management Studio / Express has an option in the GUI itself do this (if you want, scroll below to see that option), it will not always work in the way it is expected to work. So, here is a simple set of SQL Commands that does the trick and truncates the log file to a unbelievable size saving some essential space. To do that, just open a New Query window and then type the following code:

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

The first statement makes the database to perform the operations and the second and fourth commands just shows you the actual size of log being used by the database. The third statement is actually a backup statement but here it just truncates only the log file. So, if you’re afraid of loosing anything, please take a full database backup manually before executing the above commands.

If you’re not up for executing commands and you’re more of a GUI person, you can use the following steps to truncate the log file (though it may not work certain times)

1. Start SQL Server Management Studio / Express
2. Connect to your Database Server
3. Expand the Databases in the left side list
4. Right click on your Database name and choose Tasks -> Shrink -> Files from the popup menu
5. Choose File type as Log
6. Choose any of the following three options as per your need:
   1. Release unused space – Frees any unused space in the Log file (mostly this will not save any disk space)
   2. Reorganize pages before releasing unused space and choose a minimum size as you please in the Shrink file to option.
   3. Empty file by migrating the data to other files in the same filegroup
7. Click on OK
8. After execution completion, open your Database files location (you can find that from the Properties of your Database) and see whether the Log file (with extension LDF) is reduced.

If above steps doesn’t seem to work, please try the commands I’ve specified above as they’re guaranteed to work.

Let me know your if you face any problems in the comments…