Skip to main content
Sign Out

Welcome to Dell

My Account
  • Place orders quickly and easily
  • View orders and track your shipping status
  • Create and access a list of your products
Sign In Create an Account Premier Sign In Partner Program Sign In
Contact Us
Some article numbers may have changed. If this isn't what you're looking for, try searching all articles. Search articles

Microsoft SQL: Shrinking Database Transaction Log when needed

Summary: The Microsoft SQL Server Database Transaction Log receives database transactions for SQL Server databases. Sometimes these Transaction Logs become large and can potentially threaten to consume all available disk space. Resolution below includes steps for shrinking SQL Server Transaction Log files. ...

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 grown to 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.
 

  1. Use SQL Server Studio Management. Right-click the database with Transaction Log that must shrink. Select Tasks -> Shrink -> Files.

Click 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" button to complete the file shrink.
Shrink file 



 

  1. Using TSQL to shrink Transaction Log file.

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 TSQL statement 

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

Transaction log

NOTE: A common reason the SQL Server transaction log will not shrink is because there is not enough space in the log to allow the shrink operation to run.

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: 05 Dec 2024
Version:  7
Find answers to your questions from other Dell users
Support Services
Check if your device is covered by Support Services.