Shrink SQL Database large log files using TSQL

Shrink the large database log files using the following commands.

If the database is in the SIMPLE recovery model you can use the following statement to shrink the log file

DBCC SHRINKFILE (AdventureWorks2012_log, 1)

Replace AdventureWorks2012_log with the logical name of the log file you need shrunk and change 1 to the number of MB you want the log file shrunk to.

If the database is in FULL recovery model you could set it to SIMPLE, run DBCC SHRINKFILE, and set back to FULL if you don’t care about losing the data in the log.

ALTER DATABASE AdventureWorks2012
  SET RECOVERY SIMPLE
  GO
  DBCC SHRINKFILE (AdventureWorks2012_log, 1)
  GO
  ALTER DATABASE AdventureWorks2012
  SET RECOVERY FULL

You can find the logical name of the log file by using the following query:

SELECT name FROM sys.master_files WHERE type_desc = 'LOG'

You can find the logical name of your database file by using the following query:

SELECT name FROM sys.master_files

Woila... your log file is shrinked to few MB now. Enjoy.