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

First, determine whether the database requires a full or simple recovery model. Full recovery enables point-in-time restores with transaction log backups, whereas simple mode is used when you only need to recover to the point of the last full backup.

If full recovery model is required, create scheduled transaction log backups with maintenance plans or scripts.

For servers using maintenance plans, you can edit maintenance plans in SQL Server Management Studio under Management, Maintenance Plans. Your server may have multiple maintenance plans, like one for system databases and one for user databases.

If you’re implementing backups for the first time, consider using Ola Hallengren’s free maintenance scripts. They’re not quite as easy as maintenance plans, but they’re much more flexible. Ola’s scripts even work with third party backup compression products.

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, SHRINKDB may actually be right for you – but only for the log file.

Return to sp_Blitz or Ask Us Questions