How to Shrink SQL Log file

How to Shrink SQL Server Transaction Logs

To shrink a log file

Shrink a File

• In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
• Expand Databases and then right-click the database that you want to shrink.
• Point to Tasks, point to Shrink, and then click Files. ...
• Select the file type and file name.
• Optionally, select the Release unused space check box.
• Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
• Optionally, select the Reorganize files before releasing unused space check box. If this is selected, the Shrink file to value must be specified. By default, the option is cleared.
• Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.
• Optionally, enter the maximum percentage of free space to be left in the database file after the database has been shrunk. Permissible values are between 0 and 99. This option is only available whenReorganize files before releasing unused space is enabled.
• Optionally, select the Empty file by migrating the data to other files in the same filegroup check box.
• Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted. This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.
• Click OK.

You can also do by using Query


DBCC SHRINKFILE(<log_file_name_Log>)
BACKUP LOG <database> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log_file_name_Log>)

The ‘proper’ thing to do these days
is to put the database into ‘simple
recovery’ and then to shrink the log.
A few commands that I think might do
it:

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL

Some notes on SQL Server backups:
transaction logs have to be backed up in order for them to ‘truncate’. If they are not backed up regularly, they will eventually fill the disk
a full backup alone will not do the trick – that does not touch the transaction log
transaction logs must be backed up via ‘BACKUP LOG’
if the dba does not require transaction log backups, it is advisable to move to Simple Recovery