Multiple log files

Question

Post reply

Multiple log files

LondonNick

Right there with Babe

Points: 725
More actions
March 3, 2011 at 5:55 am

#247556

Hi,
I have long held the belief that multiple log files have little effect on performance as they are filled sequential and not in a round robin way. That was supported by Paul Randal here http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx
I currently have a database with three log files on a SQL 2008 sp2 database. The reason is I am doing a large update on a large table (40gb) and have limited disk space on several disks. If the log was being written sequentially I would expect to see one log file fill up and then the next. However, I am seeing all three fill up at about the same rate. How is that the case if there is no round robin? I am checking space by running:
SELECTa.FILE_ID,
[File Size(Mb)]=
convert(decimal(12,2),round(a.size/128.000,2)),
[Space Used(Mb)]=
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[Free Space(Mb)]=
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
[Db Name]=a.NAME,
[File Name]=a.name
FROMsys.database_files a
Many thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 1

AFAIK the log files are filled up sequentially. I believe following scenario in your case.

1. There are three log files ie LF1, LF2 and LF3

2. LF1 filled up.

3. LF2 started filling up.

4. log backup is taken which creates space in LF1.

5. LF2 filled up.

6. LF3 started filling up.

7. log backup is again taken and this created some FREE space in LF2.

8. When you browse the files it look like all are filled upto some extent.

BUT if you see that all are filling simultaneously.... then this is a good thing and DBAs can use more log files to share the transaction load on many drives.

-Lucky

LondonNick Right there with Babe Points: 725 More actions · Answer 2

The database is in simple recovery so there are no log backups. I can run the query above every couple of seconds and the amount of space used in all logs is going up at around the same rate. When they fill all three auto-grow at around the same time. This is a development server and no-one is using it other than me. So one update statement appears to be writing to three log files spanning three disks at the same time.

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 3

This is something really new in SQL 2008 sp2. I shall verify the BOL and let you know if i find anything.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 4

I'll have to run some tests to validate the following, but it sounds likely.

If you have created your log files at one time, a long time ago, and configured each to grow, then it's likely that your virtual log files (VLFs) have been created in an interleaved fashion. Run "DBCC LOGINFO" and take note of the FileID and the FSeqNo.

My guess is that if you order by FSeqNo, you'll see the FileID change row-to-row, which would manifest itself as the situation you're describing.

To avoid this problem (if you consider it a problem!), I would recommend only allowing one of your transaction logs to grow, and keep the others at a static size. This way, new VLFs are only ever added to a single file.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

With multiple log files, the engine will write serially through the log files. If you started in the first file, the transaction would use all the VLF's in the first file, then move the second file, write to all the VLF's, then move to the third file and write to all of its VLF's. At that point the transaction log will wrap back to the first file, and since the transaction is still open, the VLF's can't truncate for reuse, and the first file grows, allowing the log records to be written to the end. When the growth space in file 1 filles, it moves to file 2 which also has to grow, and the process repeats. I showed how to validate this in December 2010 with my blog post:

An XEvent a Day (23 of 31) – How it Works – Multiple Transaction Log Files

If you use the Extended Event session in that post, you can track the file growths and usages.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 6

Thanks for the clarification, Jon. I didn't have the same setup available to give it a quick test, and had a niggling though that it would exhaust a single log file before moving onto the next, but wasn't 100% sure. It sounded possible from the symptoms though.

Given that the update is occurring in a single transaction and causing the log to grow, DBCC LOGINFO would definitely show the order that each VLF (and thus the physical transaction log file) are being written to, to verify that they are indeed filled sequentially.