Shrink MS SQL Transaction Log File
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
- Open SQL Server Management Studio and connect to SQL Server Database Engine instance
- Now right click on the database that you want to shrink and select Tasks >Shrink > Files
- Select the File type, file group and file name
- 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
Check the Size of MS SQL Transaction Log file
- Connect to the appropriate SQL instance in SQL Server Management Studio
- Right click on the database and select Properties
- Select files under select a page column, and note the path of the file
- 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
- You have to repeat this process for each database.
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.