Using multiple log files
7November 23, 2015 by Kenneth Fisher
The transaction log is made up of one or more files that are used sequentially. So in other words if you have multiple log files then new log information will only be written within one of those files at a time.
The good
To the best of my knowledge there is only one benefit of having multiple log files. You can extend your log data across multiple drives. So for example your log file is currently on the M drive. The M drive unexpectedly starts to fill up (You’re monitoring drive free space and expected file growth right? So this has to be unexpected.) As a quick, temporary fix you can add a new log file on drive Q and your log will happily start using space on the new drive.
The bad
Honestly I don’t know of any real disadvantages of having multiple log files. Paul Randal(b/t) discusses what he feels are the disadvantages here but in my opinion those are all problems with log space management, which, while very important, is something different from the existence of multiple files. In fact if I stretch a bit I guess I could say that the one disadvantage I can think of is during recovery you have to have a location to put each of the files. This means having both drives available or having an additional MOVE clause on your RECOVER statement.
The recommendation
With one small benefit and one very small disadvantage I’m going to say stick with one log file unless you need to split across multiple drives. Even then fix your space problem (smaller log or bigger drive) and get back to a single file. This comes under the heading of simplest is better and just because you can doesn’t mean you should. Let’s face it, maintenance is easier the simpler things are so stick with that single log file.
SQL Studies 
I’ve see it a couple times where they expanded to a drive on a different tier. So the main log was on tier 0 or 1 and they expanded to tier 3. Ordinarily that wouldn’t be too big of a deal, but these were very high xact DBs and it did matter in this case.
The cool thing is how it came out in intermittent perf issues that were actually kinda hard to track down. It’s just not the first place you look.
Very true. I hadn’t thought about the fact that your drives could very well be different tier storage. Of course if they are expanding the log out to a second drive one would hope it was an emergency. At that point it’s being down vs performance issues and that’s not an unreasonable decision. Then in an ideal world they would have “fixed” the multiple log files once they had time to expand the original storage. That way they probably wouldn’t have even noticed the performance issues 🙂
While quite true, it’s often quite difficult to go back and take the log file off that new drive. Another DBA could have handled that and just didn’t tell anyone about it and then forgot himself.
And I slightly understated the issue before. Sure it can be on a lower tier drive, but most often it’s on a drive with lots of contention. So they’ll expand it from its dedicated drive and only a one that has 60 other DBs on it… and is probably a lower tier at the same time.
I wonder what the effect is on VLFs: whether multiple log files might help to reduce the number of VLFs in each file, and whether that really matters….
Your thoughts?
It has no effect on VLFs to my knowledge. Each file growth (of whichever file) will cause additional VLFs to appear in that file based on the size of the growth just like if you had one log file. Splitting the total number of VLFs across two files (or more) won’t have any effect on normal usage. Remember that new data is only being written to one file at a time. Ie the active VLF regardless of which file it’s in.
[…] it’s been committed, and to restore transactions between full backups. While you can have multiple log files there aren’t very many good reasons to. It’s generally best practice to only have one […]
Thanks for such a good information.
Thank you so much