How to shrink transaction log file for a SQL Server database?

Using the steps below one can shrink the transaction log.

  • Find the id and file name of the log in the database you want to shrink the log.
  • Use <your database name>
    SELECT file_id, name FROM sys.database_files
    GO
    file_id name
    1	DB_NAME
    2	DB_NAME_log
  • 2- Run the SHRINKFILE pointing to the file name and id.
  • DBCC SHRINKFILE ('DB_NAME_log', 2)
    GO
    BACKUP LOG TestDB WITH TRUNCATE_ONLY
    GO
    DBCC SHRINKFILE ('DB_NAME_log', 2)
    GO