Transaction Log Files Larger Than Data Files [duplicate]

Question

I'm an accidental DBA and need help determining a course of action regarding transaction log files on several databases. I ran Brent Ozar's sp_blitz to see what areas of concern there might be on an instance of SQL Server 2012 and one of the things I saw was transaction log files that are bigger than the data file. On one database the data file is at 3.33 GB and the log is 5.19 GB. The others have a ratio that's about the same.

The instructions for this scenario indicates that the log file might need to be shrunk, but only if the database is in Full recovery mode. This database is set to Simple recovery.

Should I shrink the log files or just leave them alone?

Thanks for your time.

Answer 1

Jeff, Brent here. We put a lot of documentation in that URL - make sure you copy/paste the URL into your web browser to read the full documentation.

If you're in simple recovery model, and you've got log files larger than the database, there are lots of possible causes:

• Someone started a transaction, locked their workstation, and went home for the weekend
• Someone ran a legitimately large transaction (like a huge update across the entire database, multiple times)
• The log is growing due to something other than a backup (to find out what it is, check SELECT * FROM sys.databases, and look at the log_reuse_wait_desc column)

My bigger concern would be the fact that your databases are in simple recovery model. That means you can't do a point-in-time recovery. If something goes wrong, you're restoring all the way back to your last good full backup. Businesses are often uncomfortable with that much data loss.

Answer 2

So the items in Brent's answer should be investigated first: make sure you don't have some sort of larger issue that needs fixed a different way.

But once you have done so, it is absolutely possible you will determine that there is no ongoing issue, and that the excessive log growth was due to a problem that you've since resolved. For example:

• Logs grew due to a one-time runaway query by a developer, who has been sufficiently chastised.
• Logs grew due to a one-time problem with tran log backups, which have since been fixed
• Logs grew due a problem with replication, which has since been fixed
• Logs grew because databases were in Full recovery mode, but they should have been in Simple (restoring a prod db to a dev environment, for example)

In those cases, the large log file will be almost entirely empty, and it is absolutely fine to shrink the logs to a more appropriate size.

A few notes when you do:

1. This should be a rare one-time operation, not something you do routinely. If you find yourself having to do this routinely, then check back with the items on Brent's list.
2. Always do a SHRINK FILE, never a SHRINK DATABASE
3. Shrinking data files is an entirely different matter, with serious performance implications. Don't do that, except in very rare, very carefully planned situations.
4. Always shrink with a reasonable target size in mind, never shrink to recover all free space from a log, since it would just have to immediately regrow
5. Make sure you understand the information about how VLF size impacts SQL performance discussed by Kimberly Tripp here and summarized by this DBA.SE post. In short, you might need to shrink your log down very small, and grow it in specific-sized increments.
6. Make sure the log growth setting are appropriate (never use %, never a multiple of 4GB, keep expected VLF size considerations in mind).

Your target size will vary widely by the expected activity on the database. Especially with Simple mode, you certainly wouldn't need a log that is any larger than the used data space in the database.

Once you've understood all that, then simply shrink your log with a statement like the following:

DBCC SHRINKFILE (name = 'mydb_log', size = 2000)

Which will shrink the log file down to 2GB.

It is possible an active transaction may prevent it from shrinking down immediately. If so, wait for the transaction to complete and repeat the command later.

If you are doing this to a database in full recovery mode, you will likely have to shrink, then run the log backup, then shrink again to the target size.