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.
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.
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.
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
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:
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.
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!
My Pluralsight course for new SQL Server DBAs
I see “Recovery Pending”
Is that expected?
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”
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, quite helpful.
Saved my life Kevin 🙂
Glad to help 🙂
Excellent, I solved the problem.
Chuck Hall says
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?
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
Thanks Kevin, very useful!