Shrink Microsoft SQL Server Transaction Log Safely and Effectively

Summary: Learn how to shrink Microsoft SQL Server transaction logs using SSMS or TSQL. Fix log file bloat, avoid disk issues, and maintain optimal database performance.

This article applies to This article does not apply to This article is not tied to any specific product. Not all product versions are identified in this article.
Check out other resources

Symptoms

Microsoft SQL Server Database log files grow to large file sizes. SQL Server database goes offline and is unavailable if the Transaction Log drive location runs out of space.

Cause

The most common cause of Microsoft SQL Server Transaction Logs to grow continuously is when the Database Recovery Model is set to Full or Bulk Logged. When using the Full or Bulk-Logged recovery model and the log file is not backed up, it continues to grow. File corruption may also occur resulting in the Transaction Log growing to sizes larger than expected relative to database transaction activity.

Resolution

When the SQL Server Transaction log has file sizes threatening to take up all available disk space, the file should be reduced with a shrink log operation. The steps below can be used to shrink the size of SQL Server Transaction Logs.

Use SQL Server Studio Management.

  1. Right-click the database with Transaction Log that must shrink. Select Tasks > Shrink > Files.

Click to shrink files

  1. The interface that comes up gives options for the file shrink operation. Make the selection options below:

File type - In the drop-down field, pick Log.

Release unused space - Select the radio button.

Click OK to complete the file shrink.

Make selections to shrink file

Using TSQL to shrink Transaction Log file.
  1. Run the TSQL statement below to shrink the designated database log file, replace the database log name with the name of the database log being shrunk:

Run the TSQL statement

  1. If the Transaction Log does not shrink successfully with the command above, the step below should allow the shrinking of the log. Replace database name and log file names where necessary.

Secondary shrink command


NOTE: A common reason the SQL Server Transaction log does not shrink is that there is not enough space in the log to allow the shrink operation.

FAQs:
  • How to increase the size of log file in SQL Server?

Follow the Microsoft article Increase the size of a database This hyperlink is taking you to a website outside of Dell Technologies.(this process requires ALTER permission on the database)

  • What are the disadvantages of shrinking database in SQL Server?

While shrinking a SQL log file can free up space, it can have negative consequences on database performance and fragmentation. It is best to address the root cause of excessive database growth and avoid frequent shrinking operations. 

See Microsoft article Manage file space for databases in Azure SQL Managed Instance for more information about SQL log file shrinking.

Additional Information

Shirking Your Microsoft SQL Server Transaction Log File

Duration: 00:07:55 (hh:mm:ss)
When available, closed caption (subtitles) language settings can be chosen using the CC icon on this video player.

Affected Products

Dell Quickstart Data Warehouse Appliance, Microsoft Windows Server 2016, Microsoft Windows Server 2019, Microsoft Windows Server 2022, Microsoft Windows 2012 Server R2, Prosupport for Microsoft Software
Article Properties
Article Number: 000196817
Article Type: Solution
Last Modified: 12 Jun 2025
Version:  9
Find answers to your questions from other Dell users
Support Services
Check if your device is covered by Support Services.