Shrink MS SQL Transaction Log File

Stacy Brown | November 16th, 2015 | SQL Transaction Log |

Introduction

Transaction Logs are integral part of SQL Server Database.The blog discusses on how to shrink Transaction log file in SQL Server.MS SQL Transaction Log file is a continual record of all changes made to the database. Each database has minimum one transaction log file that is exclusive to that database. But sometimes transaction log file grow unexpectedly large and contains extra unused space that are of no use. In such situations we need to shrink MS SQL Transaction Log File by doing some manual steps.

Method to Shrink MS SQL Transaction Log file

  1. Open SQL Server Management Studio and connect to SQL Server Database Engine instance
  2. connect to sql server

  3. Now right click on the database that you want to shrink and select Tasks >Shrink > Files
  4. shrink ms sql transaction log file

  5. Select the File type, file group and file name
  6. Now you have three shrink action option
    • Release unused space option releases the unused space in the file to the operating system. This option reduces the file size without moving any data.
    • Reorganize files before releasing unused space option allows you to enter free space percentage that to be left in the database after shrinking. In this option you have to specify the Shrink file to value and recognized values are from 0 to 99.
    • Empty file by migrating the data to other files in the same filegroup option moves specified file data to other filegorup files. Then you can also delete the empty file from filegorup
  7. And then click on OK button

shrink file option

Check the Size of MS SQL Transaction Log file

  1. Connect to the appropriate SQL instance in SQL Server Management Studio
  2. Right click on the database and select Properties
  3. sql server database properties

  4. Select files under select a page column, and note the path of the file
  5. database file path

  6. Check is there any transaction log file larger than their data file. If the transaction log file is 20% or more larger then you will need to shrink the file
  7. You have to repeat this process for each database.

Conclusion

This article describes how to shrink MS SQL Transaction Log file using SQL Server Management Studio. The log file shrinking can orrur when the database is online and has at least one free virtual log file. It is very important to shrink these files before they consume all the available space.

Useful Links

Become a Writer
Do you have flair of writing for SQL Server & other Database related Technology
We welcome the technical post from bloggers & technical writers who can contribute interesting stories about SQL Server on regular basis. If you think you are a good fit, then this SQL Server Explorer is a Platform for you. Stay in Touch with us.