Using multiple log files

5

November 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.

5 thoughts on “Using multiple log files

  1. 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 🙂

      • midnightdbasean says:

        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.

  2. Mark Starr says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: