Information Technology Knowledge Base

Your transaction log is huge and has free space.
So you want to shrink it and it shrinks very little. You can solve this by running following script.

USE your_db
GO
DBCC SHRINKFILE(your_log, 200)
BACKUP LOG your_db WITH TRUNCATE_ONLY
DBCC SHRINKFILE(your_log, 200)

WATCH OUT
The TRUNCATE_ONLY which we use and the NO_LOG options of the BACKUP LOG statement will be removed in a future version of SQL Server. These options remove the inactive part of the log without making a backup copy of it, and truncate the log by discarding all but the active log. This breaks the log chain. Until the next full or differential database backup, the
database is not protected from media failure. Therefore, we strongly recommend that you avoid using either of these options in new development work and that you plan to modify applications that currently use it.
Your transaction log is huge and has no free space.
First we need to find out why it’s in use.

SELECT
name,
log_reuse_wait,
log_reuse_wait_desc
FROM
sys.databases;

Read Factors That Can Delay Log Truncation, this page explains the result.