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

    Copyright 2020. All Rights Reserved