sp_Blitz Result: Transaction Log Too Large?
A transaction log file that is larger than the data file can indicate that transaction log backups are not being performed or are not being performed often enough.
This part of our SQL Server sp_Blitz script checks the size of each log file and compares it to the total data file size for the database.
To Fix the Problem
Read the details below for information on how to:
- Make sure you’re in the right recovery model
- Set up transaction log backups if you need point in time recovery
- Shrink your transaction log file (maybe)
How to Fix Giant Transaction Log Files
Check Your Recovery Model
First, determine whether the database requires a full or simple recovery model.
- Full recovery enables point-in-time restores with transaction log backups
- Simple mode is used when you only need to recover to the point of the last full backup.
Set Up Transaction Log Backups
If full recovery model is required, create scheduled transaction log backups with maintenance plans or scripts. Follow the instructions here.
Shrink the Log File (maybe)
Once the backups are under control, you could consider shrinking the log file back down to a reasonable size. We raaaaarely recommend shrinking the log file, but this could be one of those scenarios. If the log grew out of control because the database was in full recovery mode and nobody was taking backups, DBCC SHRINKFILE may actually be right for you – but only for the log file.
Here’s an example command:
/* Use the database */ USE [GiantLogs] GO /* Check the name and size of the transaction log file*/ /* The log is fileid=2, and usage says "log only" */ /* Bonus: make sure you do NOT have more than one log file, that does not help performance */ exec sp_helpfile; GO /* Shrink the log file */ /* The file size is stated in megabytes*/ DBCC SHRINKFILE (GiantLogs_log, 2048); GO /* Check if it worked. It won't always do what you want if the database is active */ /* You may need to wait for a log backup or more activity to get the log to shrink */ exec sp_helpfile; GO