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.
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!
My Pluralsight course for new SQL Server DBAs
