• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

Why is my SQL Log File Huge?

March 8, 2017 by Kevin3NF 11 Comments

Pluralsight courses for new SQL Server DBAs
Do you need our help?     Or our DBA retainer service for emergencies?

 

HUGE Log files and how to troubleshoot:

The single most common question I have encountered in 18+ years of working with SQL Server:

Why is the .LDF file filling up my 500GB drive?  I only have 100MB of data!!?!?!?  Why am I getting error 9002?

For new or non-DBAs, this is a very frustrating situation without a logical reason (or so it seems).  It is also very common for it to be accompanied by applications that won’t work, alerts firing for drive space issues, etc.

If you like video, I recorded my response to this question and discuss the two most common remedies.  If you don’t like video, scroll down for text:

 

There are a number of reasons a log file can fill to extreme sizes.  The most common one by far is that the database is in full recovery model, and Transaction Log backups are not happening fast enough, or not happening at all.  Next to that, it could be that you had a massive transaction happen such as a huge data import, rebuild all indexes, etc.  These are logged and stay there until the .ldf file is backed up (or checkpointed if you are in Simple Recovery).

Step 1: Verify recovery model

Right-click the database, go to properties, click the Options tab.   You will see Full, Bulk-Logged or Simple.   If you are in Full, you have the option of backing up the log…which is the best possible situation.

SQL Server Database Options

Step 2: Verify if the log is full or “empty”

Verify if the log file is actually full or not.  If you are backing up and the file still grew to ridiculous size…it may have just been a one time thing and you can deal with that easily.  Right-click the database, go to reports, standard reports, disk usage.  This will give you 2 pie charts.  Left is the data file, right is the log.  If the log shows almost or completely full AND the huge size, you need to backup.  If the log file is huge and mostly empty, you simply need to shrink to an acceptable size.

SQL Server Disk Usage

Step 3: Shrink the file (if empty)

Right-click the database>>Tasks>>Shrink>>Files

Choose ‘Log ‘ from the File Type drop down.  Hopefully there is only one log file.  If not, pick the big one.  Under Shrink Action, choose an appropriate size and ‘Reorganize pages before releasing space” option, even though log file shrinks don’t actually do that.   Pick a size in MB and click ok.  0 is not a good choice here.

SQL Server Shrink File

Step 4: Backup

I’m not going to go into a ton of detail here….Right-click the database>>Tasks>>Backup   Change the backup type to Transaction Log and work through the rest of the steps.

If the Log Backup works, but the space is not freed (refresh the usage report), you have a different issue that these steps will not help with. Check out the “Wrapping Up” section at the bottom of this post.

If you don’t have enough room on any local, attached or network drive to create a log backup, even with compression, keep reading:

Step 5: Flip the Recovery Model (if log backup is not possible)

Warning:  Doing this WILL cause you to lose point-in-time recoverability, but if you cannot backup the log, you are pretty much already there anyway.

Right-click the database>>Properties>>Options

Change the recovery model to Simple and click OK

SQL Server Recovery Model

Wait a few seconds and then go refresh the Disk Usage report.  The log file will be the same size, but should be almost empty:

SQL Server Disk Usage

Step 6: Shrink the Log file

See step 3 above…

Step 7: Flip the recovery back to Full

See step 1…

Step 8: Set up recurring log backups

If you don’t know how to do this, go to Management, Maintenance Plans, right-click Maintenance Plan>>Maintenance Plan Wizard and go from there.   This is well documented elsewhere.

Wrapping Up:

Hopefully, this resolved your issue but there are definitely other reasons for this issue to happen aside from a simple failure to back up.   Most notably, a very large transaction in a database that is participating in SQL Replication as a publisher.

If the above methods do not work, run these two statements and go post the results in the MSDN SQL Server forums, along with a description of the issue and what you have already tried (hopefully all of the above):

Select [name],recovery_model_desc, log_reuse_wait_desc 
from sys.databases
Where [name] = 'MyDatabase' --change this

DBCC OPENTRAN --results will be in the messages section

I love comments on my post, but if you need quick help go to the forums first, or maybe even a call to Microsoft Support if the “quick hits” don’t get you the resolution you need.  If this helped, please comment and share the link…

Thanks for reading!

Kevin3NF

My Pluralsight course for new SQL Server DBAs

Follow @Dallas_DBAs

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: Accidental DBA, backup, Beginner, EntryLevel

About Kevin3NF

Reader Interactions

Comments

  1. Frank says

    June 4, 2020 at 5:41 am

    Hello,
    I see “Recovery Pending”
    Is that expected?
    tnx

    Reply
    • Kevin3NF says

      June 4, 2020 at 9:39 am

      Hi Frank,
      Backing up and Shrinking Log files should not leave a database in that status, as they only work is the status is “Online”, and neither will change that. There is something else going on to see “Recovery Pending”

      Reply
  2. derek says

    August 8, 2020 at 10:30 am

    Hello,
    one of database hourly transaction log backup file size jump to big 6GB (normal is 10MB).
    Restore DB up to this tran log backup to understand what are those transactions.
    FROM sys.dm_db_log_space_usage returns total log size is 164GB and used size is 6GB.
    But function fn_dblog(null,null) only returns 600 rows.
    What is the best way to get what transactions caused huge log file grew?

    Thanks

    Reply
  3. Mehta says

    August 25, 2020 at 11:51 am

    Thanks, quite helpful.

    Reply
  4. Johnathon says

    September 8, 2020 at 6:23 am

    Saved my life Kevin 🙂

    Reply
    • Kevin3NF says

      September 8, 2020 at 8:00 am

      Glad to help 🙂

      Reply
  5. Kato says

    October 15, 2020 at 4:23 am

    Excellent, I solved the problem.

    Reply
  6. Chuck Hall says

    November 18, 2021 at 3:09 pm

    Hi Kevin3NF,
    Great video on dealing with huge log files. My question is, why doesn’t the log file shrink to near 0 when I do a full backup? Aren’t all the previous transactions recorded at that point?
    Thanks,

    Reply
    • Kevin3NF says

      November 18, 2021 at 3:14 pm

      A full backup is basically the data file, not the log (oversimplified)

      If you do a LOG backup, the .LDF is almost empty. BUT, it does not shrink, because it would most likely need to grow again, and growing log files is resource intensive. Ideally, regular LOG backups, and whatever size the file gets to is what it needs. Some maintenance operations like Index rebuilds use a LOT of space in teh T-log

      Reply
  7. Dave says

    February 17, 2023 at 2:26 pm

    Thanks Kevin, very useful!

    Reply

Trackbacks

  1. Handling Overly Large Log Files – Curated SQL says:
    March 10, 2017 at 7:16 am

    […] Kevin Hill shows how to recover from a scenario with an unexpectedly large SQL Server transaction lo…: […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • HealthCheck
  • Index
  • Install
  • IRL
  • Management
  • Migration
  • MySQL
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • Podcast
  • PowerShell
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • Upgrade
  • vendor code modify
  • video
  • vNext

Copyright © 2023 · Genesis Sample on Genesis Framework · WordPress · Log in

Privacy Policy
 

Loading Comments...