0

I'm studying SQL Server. I have read that a database can have several data files and one log file, but recently I was heard that a database can have more than one log file.

Is this true?

Mat
  • 10,079
  • 4
  • 42
  • 40

2 Answers2

3

Having an additional log file doesn't actually hurt performance in and of itself. That doesn't however mean that you won't have performance issues when you have multiple files. If one of your files is on slower storage for example you could end up with intermittent performance issues which can be difficult to troubleshoot and cost you quite a bit of time. Also frequently when people use a second log file they end up with a larger log than they actually need. As pointed out by Paul Randal in the link in the comments this can cause you significant issues during recovery.

The important thing to remember is that the system writes to one specific point in the log, and writes sequentially. So if you have multiple log files you are only writing to one file at a time. This means that having more than one log file is generally pretty useless.

The only reason I've found that you actually want to have a second log file is to temporarily extend your log onto a second drive. You've run out of space for your existing log file and don't have time to add space to that drive. You then create a second log file on a different drive with more space. This should be a temporary situation however. You'll want to expand your existing space (or create a new one) and move your log back into a single file.

I wrote about it in more detail here if you are interested.

Kenneth Fisher
  • 24,127
  • 12
  • 61
  • 114
  • See Paul's point about recovery - http://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/ – Aaron Bertrand Feb 10 '16 at 15:20
  • I have. I just tend to disagree. He's talking about an unnecessarily large log file(s). It doesn't matter if you have one 2 gb log file or two 1 gb log files. Same amount of space has to be zero initialized taking the same amount of time. I agree that extra log files should be gotten rid of (and only used as a temporary fix to a problem) just disagree with his reason. – Kenneth Fisher Feb 10 '16 at 15:50
  • I think you're splitting hairs, because if someone has a 2 GB log file and then has some emergency or genius idea that they should have another log file, they're not going to shrink the first one to 1 GB and create a new one of 1 GB, they're going to add one of 2 GB (or more). – Aaron Bertrand Feb 10 '16 at 16:00
0

Its possible to add more than one log file but it doesn't do any good. In fact, it generally hurts performance and just adds overhead (plus anyone that does so ends up looking like they have no idea about SQL Server).

Rich
  • 21
  • 4
  • 1
    Doesn't really hurt performance since they are only written in sequence (i.e. both files are not active at the same time). Administratively though, multiple log files are unnecessary overhead. – Mark Broadbent Feb 10 '16 at 14:33
  • Well, @MarkBroadbent, there are scenarios where multiple log files can impact recovery time - http://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/ – Aaron Bertrand Feb 10 '16 at 15:19
  • Hi @AaronBertrand , thanks. That's perhaps an availability consideration, but not necessarily a performance one. Even then I'd expect recovery time of a 100GB log file would be no more than 2*50GB logs, but Paul's point is obviously to size the log no more than is appropriate and (if I am paraphrasing him correctly) that the existence of a second log is usually the result of an unnecessary addition due to bad log management in the first place. – Mark Broadbent Feb 10 '16 at 16:11
  • @Mark It's still a performance issue IMHO, just at a different time than traditional log writes. Your 100GB to 2x50GB comparison is unfair - that assumes that everyone with a 100GB log file who has to add a second log file will shrink the first log file so that their overall log file size stays the same. Have you ever actually seen anyone do that? If they had the ability to shrink the first log file, they wouldn't have needed a second log file. I still think it's valid to point out the recovery issue and slightly misleading to wave hands and say "no possible performance issue here, never." – Aaron Bertrand Feb 10 '16 at 16:38
  • @AaronBertrand I think the point I was making was that the recovery overhead is related to the amount of log that needs to zeroed-out rather than because it is multiple-log files, they are not mutually exclusive. Obviously the presence of a second file would infer poor tlog maintenance has occurred and therefore more log to zero than would really be necessary in a well run environment so I will definitely concede that you are right to point this out -so thanks :) – Mark Broadbent Feb 10 '16 at 16:59