8

Is It normal to keep log file larger than data file?

I know why my log file is large, it is because I have a huge modification and locking occurred on a specific time, and caused my log file to be large.. as i'm using log shipping, I normally take log backups every 10 minutes.

What I'm asking is "Is it normal to see Log file larger than data file as my data file is about 7,216 GB and my log file is about 9,930 GB?" I'm afraid there is a standard ratio between log and data file? I don't want to shrink my log file because I have enough space on my hard disk.

Improve this question
2
  • 1
    The only downside of large log file is that it can affect your RTO, both restore and recovery (assuming large numbers of VLFs as well). But 10 GB is probably nothing to worry about.
    – dean
    Oct 2, 2019 at 5:01
  • 1
    @mustaccio That doesn't seem to be a duplicate. The other question doesn't deal with what size to expect.
    – Laurenz Albe
    Oct 2, 2019 at 6:28

2 Answers 2

Reset to default
12

If you have a huge modification going on, then yes, it can be normal to have a log file larger than your data file. After the huge modification is over, and the log backup is done, the file will not shrink back to it's original size however. But the file will be empty.

You can see this in Management Studio, if you right-click on the database and select Reports->Disk Usage. see log file Unused at 99.4% in this example


I don't recommend shrinking your log file as it will only grow back the next time it needs space. But, if you are running out of disk space, know that it is possible to shrink your log file. I just don't do it, as the space gets used every time it needs to and as it's a better practice to leave it as it is.

Improve this answer
1
  • 1
    The other reason to truncate is if monitoring tools auto-create Incidents in your ticketing system, and the process for changing the threshold on a single disk is onerous and opaque. So you truncate the file just to shut them up.
    – RonJohn
    Oct 2, 2019 at 7:00
5

It may be unusual to see log files much larger than the data in a properly configured server with well behaved applications, but it isn't wrong. SQL Server assumes that because at one time the log file needed to be grown that long that it will need that much space again so keeps it that long unless told to do otherwise.

I'm afraid there is a standard ratio between log and data file?

There is not. There are circumstances where you would expect the log file to be relatively large even without one-off large operations like the one you mention (for instance: any small database configured for full recovery, that sees a great many insert/update/delete operations over that small set of data between each log backup).

I don't want to shrink my log file because I have enough space on my hard disk.

If the operation that caused the log file to balloon is genuinely a one-off or otherwise a rare event then there would be no harm in truncating it (just truncate, not rearrange, and down to a size that still leaves plenty free for expected growth) to free filesystem space, but if you don't need the filesystem space to be freed then I wouldn't bother as the space being allocated for possible future use is not causing issues.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.