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.

András Váczi
  • 31,778
  • 13
  • 102
  • 151
Ayman Farouk
  • 101
  • 1
  • 5

2 Answers2

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.

Danielle Paquette-Harvey
  • 2,099
  • 1
  • 17
  • 31
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.

David Spillett
  • 32,593
  • 3
  • 50
  • 92