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.

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

Leave a Reply