Truncation is not Shrinking

While working with transaction logs SQL Server is recording every operation in the log file. If log records were never deleted, it would fill all the disk space. Log truncation is process which automatically frees space in the logical log for reuse by the transaction log. Many developers and admins expect that truncation also frees the physical disc space. This is a fatal mistake. Truncation frees the space inside of the transaction log file, but the file remains in its physical size unchanged. The transaction log file is so called Wrapped-Up logical file, which contains a number of virtual

Usually truncation automatically frees space in the logical log except when it is delayed. Moreover one part of the transaction log file will be truncated only if its content is backed up in the transaction log backup. Note that truncate is not done in a case of WITH COPY_ONLY backup!

Truncation occurs automatically under the simple recovery model when database is backed up. Under the full recovery model truncation is done when the transaction log is backed up.

To reduce the physical size of the transaction log file you need to perform shrinking operation. Shrinking can occur if the database is online and, at least one virtual log file inside of logical transaction log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.
To shrink the database do as shown at the next figure.



This brings you to selection what to shrink. Two options are possible: Data or Log. The first one means shrink database and the second one shrink of the transaction log.

The UI shown above and at the next figure shows exactly the status of logical file and the status of virtually used space inside of the logical file.
For example, next figure shows the physical log file whose virtual logs take  89% of physically allocated 2,25MB.


To get the feeling how shrinking work, let’s perform the shrink operation by reorganizing of unused space to zero (set “shrink file” to to 0).

 
As you see the log file is not reduced to zero size, because the backup has not been done. The used data remains in the transaction log and unused data of 11% (0.25MB) has been thrown away.

If you now perform the log backup and then perform shrink following is the result.

You will see that the log file now contains 76% of unused space with the same physical size of 2 MB.
Now you can choose to reduce the size for example to zero.
This is the way how to reduce the size of log file. Unfortunately I have experienced, that shrink in some cases does not really reduce the size to the size have have chosen.

There are two workarounds

Before you perform these steps make FULL BACKUP!

Attaching to MDF without of LOG file:

-Detach the database which you need to truncate the transaction log.

-Open the folder containing transaction log file like "\Microsoft SQL Server1\MSSQL\Data\"

-Rename the transaction log file to a different name.

-Attach the database again by attaching to MDF file like \Microsoft SQL Server1\MSSQL\Data\MyDb.MDF'

Attaching to MDF when LOG file does not exist will cause creating of the new empty log file.

Temporary Using of Simple Recovery model

Additionally you can set the database to use Simple Recovery Model. This will create log file with zero size.

After that you can switch back to Full Recovery Model.