How To Shrink SQL Server Transaction Log

Before shrinking the SQL Server log file, I recommend you read articles “What is SQL Server Transaction Log” and  “What is Database Recovery Model” that related with this file to understand what this file is.

“Shrinking the log file” means that take unused space in the transaction log file back to the operating system.

For example, we have a transaction log file with a size of 100 GB and 99 percent of this file is empty.

By shrinking, you can reduce the size of this file to 1 GB.

Under normal circumstances, you do not need to shrink the log file.

Because even if we shrink, since the file reaches that size it is very likely that it will reach the same size again.

When To Shrink Transaction Log File

Case 1

In some cases a large transaction can increase the log file size. For example 100 GB to 500 GB.

And if is a transaction that run only once, transaction log file size will be 500 GB unnecessarily.

In such a case it would make sense to shrink the log file to reduce the size to 100 GB.

Case 2

In a second scenario, the number of vlf in the log file is too large, so the recover and restore times may be too long.

In such a case, you can shrink and set a healthy auto growth value.

For more detailed information on this process, I would recommend reading the article named “Vlf (Virtual Log File) Count“.

As an important note, you should not mistakenly shrink the data file while trying to shrink the log file.

Data file can be shrinkable, but this should be avoided if there is another solution.

I would recommend reading this article on “How To Shrink SQL Server Data File“.

Shrink the Transaction Log File

Right-click on the database and click Tasks-> Shrink-> Files as follows.

In the screen that appears we choose below options;

• “Log” from the File type
• The log file name we want to shrink from the File name (there can be multiple log files)
• “Reorganize pages before releasing unused space” from the “Shrink action”

After choose the above options, click “Script Action to New Query Window” as below.

As you can see there is 0,60 mb free space in available free space. This means that our file is 60% empty.

The current size of the file is shown in the “Currently allocated space section”.

When we select the reorganize pages before releasing unused space, 1 MB(this is currently allocated space) value is shown in the “shrink file to:” section. You should type the value in which you want to reduce the file instead of the value here.

When we get the script like above, it gives us the following script.

The script you see above means that the size of the Test_log file will be reduced to 1 MB. We can perform shrink operation by decreasing this value. In our example, it is not necessary to do this because our file is too small. But if our file had a size of 1 GB and there was an empty space of 60%, we could shrink it by 60% by typing in the value we wanted.

The article entitled “SQL Server Does Not Truncate the Transaction Log After Log Backup and Can Not Shrink the Transaction Log” may also interest you.