Shrink MS SQL Transaction Log File in the Database Easily

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 execute the MSSQL shrink log file task.

Table of Contents

• Common User Queries
• Why Shrink SQL Log File?
• Shrink Log with the SSMS
• Use the T-SQL CMD Method
• Check the SQL Log File Size
• Conclusion

Shrink Log File SQL Server Common User Queries

There are several user queries worldwide that focus on this particular issue. Let’s look at these user queries to find out what users struggle with. It will help us all understand the pain points of users in depth. Which, later can help us find & learn how to shrink SQL transaction log without any hassles.

Query – 1. Without Backuping

Query – 2. Not Reducing Size

Query – 3. Increase Performance

Now, that we have gone through these user queries, it’s time that users move ahead with the solutions & other crucial aspects that users must know before they step into the procedure.

SQL Server Shrink Log File All Critical Cause

When users set their recovery model to full or bulk mode, then there are most chances of logs growing rapidly while reducing the storage space. When users opt for the Full or Bulk Logged recovery model, & they don’t have a backup of the log file, it tends to grow.

Apart from this, in case, users have any issues with their SQL Log files, that might result in unnecessary log file storage & reducing the space for major data. Moreover, corruption or other severe issues can take place which puts the entire database at risk.

Therefore, if users have such issues in their SQL log file, they can rely on the Log Analyzer solution. This way, users can know how to shrink SQL transaction log without facing any major challenges.

Download Now Purchase Now

Shrink MS SQL Transaction Log File With SSMS Method

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.

The T-SQL CMD Manual Method for Users – Very Technical

If users are quite proficient in SQL Server technicalities, they can even trust the utmost manual method using the T-SQL commands. This method is not advised to new users that are not confident.

Now, in case users have their database in the SIMPLE recovery model, they can easily use the below command for SQL shrink log file task:

 DBCC SHRINKFILE (Test_Database_log, 1)

Here, we have used the demo database, to use this command, just replace the Test_Database_Log with the logical name of the file you want to shrink. Moreover, change the number 1 to the size in MB you want to shrink the log file.

In case, the user’s database is not in the Simple Mode, they have to first set it to the Simple Mode, Execute the above-mentioned command & then put it back to the original state.

ALTER DATABASE Test_Database
 SET RECOVERY SIMPLE
 GO
 DBCC SHRINKFILE (Test_Database_Log, 1)
 GO
 ALTER DATABASE Test_Database
SET RECOVERY FULL

If users are not aware of the logical name of the log file they’re using, they can trust the below-mentioned command to find the logical name.

SELECT name FROM sys.master_files WHERE type_desc = 'LOG'

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.

Also Read: How to Clear Transaction with DBCC Shrinkfile – Complete Process

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.