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.
Filed under: Microsoft SQL Server 
