«

»

Print this Post

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.

About the author

Robert Borges

About Robert...

I have been in the IT industry since 1993 focusing mainly in 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. I also worked on my first migration to NT 4.0 back then.

From there I went on to work with Novell 3.x and 4.x along with Windows domains and active directory environments. Working my way up from technician, to specialist, to administrator, and eventually all the way up to Sr. Engineer. I spent many years working for 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 7 and Windows Server 2008 R2);various virtualization technologies (Hyper-V, Virtual Server, Virtual PC, VMware, etc…); MS-SQL server 6.5- 2008 R2; Exchange 4-2010, and much more.

I am now Director of Information Systems at Bay State Integrated Technology focusing on cloud computing and IT service, with expertise in: IT Infrastructure & Architecture, IT Security, and Cloud Computing platforms & technologies (SaaS, PaaS, and IaaS).

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 pretty 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: http://www.robertborges.us/2015/04/questions-answers/how-to-shrink-large-sql-transaction-log-files/

Leave a Reply