How to Shrink Large SQL Transaction Log Files

sqlserverSQL transaction log files should be maintained automatically by SQL Server, but sometimes grow too large when automatic maintenance is not being performed correctly. When this happens, transaction log files can grow unexpectedly large. It is important to take manual steps to shrink these files before they consume all available disk space.

How to Shrink a SQL Database’s Transaction Log File:

NOTE: This process should be run for each database with a large transaction log file.

  1. Open SQL Server Management Studio and login to the proper SQL instance.
  2. In the Object Explorer tree, expand the Databases folder, and select the database which has a large .LDF file.
  3. Create full backup of database
    1. Right-click on the database and select Tasks >> Back Up….
    2. Make sure Backup type is set to Full.
    3. Remove any existing destinations, then add a new Disk destination. Browse to a location with plenty of free disk space, and name your backup file with the .BAK extension.
      NOTE: This backup will only be needed for this process, and can be deleted in a day or two.
    4. On the Options page, select bullet option for Overwrite all existing backup sets.
    5. Click on OK button to start backup.
  4. Create Transaction Log backup of database
    1. Right-click on the database and select Tasks >> Back Up….
    2. Make sure Backup type is set to Transaction Log.
    3. You can keep the existing .BAK destination created in step #3c.
    4. On the Options page, select bullet option for Overwrite all existing backup sets.
    5. Click on OK button to start backup.
  5. Shrink Transaction Log File
    1. Right-click on the database and select Tasks >> Shrink >> Files.
    2. For File Type, select Log.
    3. Click OK button.
  6. Repeat steps 3, 4, and 5 until the .LDF file is noticeably smaller.

How to Check Size of Transaction Log File

NOTE: This process should be run for each database.

  1. Open SQL Server Management Studio and login to the proper SQL instance. If there are multiple instances, you may need to repeat this process for each.
  2. In the Object Explorer tree, expand the Databases folder, and select one of the production databases. Right-click on the database and select Properties.
  3. In the Database Properties window, click on the Files page and make note of the path of the files.
  4. Open Windows Explorer and browse to the path obtained in step #3.
  5. Check for any transaction log files larger than their data files.
    Data files are named with an .MDF extension.
    Transaction log files are typically named with an .LDF extension. These files are typically smaller than the .MDF file they belong to. If a transaction log file is more than 20% larger than its .MDF counterpart, you will need to shrink the file.

Robert Borges

About Robert...

I have been in the IT industry since 1993 focusing mainly on networking. Though I got an early start as an amateur computer enthusiast and wrote my first database app at age 12, I started my professional career working in the MIS department of one of the largest liquor distributors in the northeast. I started out there as a systems operator on the company’s two mainframe systems. From there I moved into PC support, and help design and implement the company’s first client-server network… This was back in the days of Win NT 3.51 when I worked on my first migration to Windows NT 4.0 server.

From there I went on to work with Novell 3.x and 4.x along with Windows domains and Microsft's brand new Active Directory. Working my way up from technician to specialist, to an administrator, and eventually all the way up to Sr. Engineer. I spent many years working for MSPs/consulting firms, 9 of which I owned and operated my own firm.

Over the years, I have worked with (at an expert level) various versions of Windows client and server operating systems (including Windows 11 and Windows Server 2019); various virtualization technologies (Hyper-V, VMware, etc.); MS-SQL server 6.5- 2014 R2; Exchange 4-2019, and much more.  Over the years I have built a lot of experience around the Microsoft Azure and Microsoft 365 cloud environments.

I am now CTO at Infused Innovations where our team is focusing on helping clients build a Secure Intelligent Workplace through InfoSec (Zero Trusts Framework), Modern Workplace, and Business Intelligence.

I have been heavily involved in the IT user group community, including serving as president of the board of Boston User Groups, Inc., and president of IT-Pro User Group. In 2017/2018 Microsoft awarded me the Microsoft MVP (Most Valuable Professional) Award, with a focus of Microsoft Azure cloud, for my efforts in the IT community.

I am in a constant state of learning about new products, and new versions of products. Many of which we end up implementing in lab environments and sometimes for our clients. I have a very broad range of expertise and experience. It is my goal to share some of this experience on this blog to help enrich the IT community.

Permanent link to this article: https://www.robertborges.us/2015/04/questions-answers/how-to-shrink-large-sql-transaction-log-files/

Leave a Reply