Shrink MS SQL Transaction Log File
Table of Contents
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. Let’s move further to shrink log file SQL server with ease.
Method to Shrink MS SQL Transaction Log File
1. Open SQL Server Management Studio and connect to SQL Server Database Engine instance.
2. Now right click on the database that you want to shrink and select Tasks >Shrink > Files. Then move further to learn how to shrink log file in SQL server in the continuity.
3. Select the File type, file group and file name.
4. 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. This SQL server shrink log file option is commonly used.
- 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 the filegroup. Here, users don’t really shrink SQL log file but delete them by transferring the data somewhere else.
5. And then Click on OK button.
Check the Size of MS SQL Transaction Log File
Now that we know how to shrink log file SQL server, it’s time to learn the ways to check the size of the log file. Below is the process explained for that in a simplified manner.
1. Connect to the appropriate SQL instance in SQL Server Management Studio.
2. Right-click on the database and select Properties here.
3. Select files under select a page column, and note the path of the file. SQL shrink log file is an easy task & this is even easier than that.
4. Check is there any transaction log file larger than their data file. If the transaction log file is 20% or larger then you will need to shrink the file.
5. 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. We hope that now users can easily execute the task as they know how to shrink log file in SQL server without facing any errors.