Shrink Transaction Log File In SQL Server

SQL Server shrink transaction log file

If you are often doing data migration or data scrubbing in an SQL Server database, you will notice the transaction log file grows abnormally. After the operation is complete you may need to shrink transaction log file. There are multiple ways to shrink the database log file. (See my other article for details on shrinking data file, i.e. the mdf file.)

The easiest and safest way is to use the DBCC SHRINKFILE transact-sql method to shrink just the transaction log file without affecting the database file. Another way is to use the Shrink File GUI in SSMS. I’ll go through these methods one by one. Before shrinking the transaction log, be aware of the best practice and follow them.

Best Practice

  • Take backup of the database before shrinking the transaction log.
  • Avoid shrinking the database file or transaction file repeatedly. repeated shrinking may increase fragmentation.
  • Shrinking will be efficient after completing a major database operation like data migration, data scrubbing, etc.
  • The database require free space for its regular operation. So, even after shrinking the database, the files may increase in size. So, leave some empty unused space in the database file or transaction file for performing regular operation.

Using Query Editor (Transact-SQL)

  1. To start with take a full backup of the database.
  2. Take a transaction log backup using this query.

    Query Template

    USE {{Database Name}}
    GO
    BACKUP LOG {{Database Name}} TO DISK = '{{Backup Location URI}}'
    GO
    

    Example

    USE WideWorldImporters
    GO
    BACKUP LOG WideWorldImporters TO DISK = 'C:\Temp\WWI.trn'
    GO
    
  3. Get the name of the transaction log file name using this query.

    Query Template

    USE {{Database Name}}
    GO
    EXEC sp_helpfile
    GO
    

    Example

    USE WideWorldImporters
    GO
    EXEC sp_helpfile
    GO
    

    shrink transaction log file 02
    In this example WWI_log is the name of the log file.

  4. Now execute DBCC SHRINKFILE. Include the log file name in the shrink file query. The target size is optional. But it’s good to specify a target size to give a reasonable free space for regular operations.

    Query Template

    USE {{Database Name}}
    GO
    DBCC SHRINKFILE('{{Log File Name}}', {{Target Size in MB}})
    GO
    

    Example

    USE WideWorldImporters
    GO
    DBCC SHRINKFILE('WWI_Log', 10)
    GO
    

    shrink transaction log file 03

Using GUI

  1. Login to SSMS.
  2. In the Object Explorer, expand the Databases folder.
  3. Select the database whose log file you want to shrink.
  4. Right click the database and select Tasks >> Shrink >> Files.
    shrink transaction log file 04
  5. In the Shrink File window, choose the file type Log from the File Type drop down box.
  6. In the Shrink action section, select the option Reorganize pages before releasing unused space.and enter the space you want to leave free.
    shrink transaction log file 05
  7. Click Ok.

Reference

 

 

Leave your thoughts...