Introduction
I’m at a client getting ready to give my .02 cents on why their SQL Servers are not running to well, and I happened to notice multiple transaction log files for their databases. We know that multiple data files for some databases, including tempdb, has advantages in performance and administration. This is well documented. However, I was wondering why their setup included multiple log files, so I started thinking about this; I had not remembered reading about extra throughput or anything when adding multiple transaction log files to a database, so I wasn’t sure what their goal was in having this setup. Actually seemed like a waste of resources to me. So, I decided to find out more about the transaction log (can I call it the “tlog” from here on out? Thanks) as it relates to single vs. multiple files, and exactly what happens, or doesn’t happen, when these additional files present.
Ah, yeah. It's just we're putting new coversheets on all the TPS reports before they go out now
I thought that I would find some good info on MSDN or around the internet, but was surprised a bit to find little information on this particular subject. So I called up a friend at Microsoft and his answer was:
SQL only writes to one ldf file for a database at a time. Multiple log files are only useful for working around running out of space on your log volume. [1]
What he’s saying here is that extra log files are normally used as a fail safe for the first tlog – SQL Server does not write to all tlog files at the same time, nor does it do round-robin type writes as it does with multiple data files. Then I did happen to come across some work from Paul Randal on sizing the transaction log, and he also mentions the same thing for the most part:
Add more transaction log files. As a general strategy for managing the size of the log, this is not good. Extra log files have zero effect on performance (the myth that SQL Server writes in parallel to the log files is just that - a myth) and make management more tricky. However, if your log fills up for some out-of-the-ordinary reason, then adding another log file may be the only way to allow the database to keep running. If you have to do this though, make sure that you remove the extra log file(s) when you can to keep log management uncomplicated.[2]
I’m not certain what management complication he’s referring to, other than you would have multiple log files to back up maybe? Not sure. Anyway, the first part of the paragraph pretty much matches what my buddy offered up. And I agree here – over-engineering anything is worthless, so why do it? And that’s exactly what I’m trying to root-out here.
By the way, Paul Randal has got some really great stuff on his blog, very impressive. I read his bio and see that he was with Microsoft for 1000 years, so I figure that he knows what he is talking about. I’ve also read some of his articles as a subscriber to SQL Server Magazine for the past decade, so I know he’s good with SQL Server internals as well. Check out his blog postings on the transaction log [2] for some really cool threads.
Want to go to Chotchkie's? Get some coffee?
Being the curious one that I am, all of this info is good and wonderful, but I wanted to investigate just a bit deeper to see if SQL Server really just ignores these files, whether SQL Server writes to them or not, or whatever else I might be able to dig up.
I created a database called Test, and it looks kind of like so:
Notice that I’ve shrunk the logs to 1MB, but you can create them this size to start off. I also have them on different vhds, so I know that I/O won’t be running together in case we find something here in our little investigation.
Run the following to verify that the log files are all 1MB:
SELECT (size * 1.0 * 8.0)/1024.0 AS size_in_mb,*
FROM Test.sys.database_files
WHERE data_space_id = 0
I now execute the following script and insert some data into a table called LogTable. It has two columns, one of decimal data type, and the other char data type:
SET NOCOUNT ON
DECLARE @cnt decimal(10,4)=0
DECLARE @rows int=0
BEGIN TRAN
WHILE 1=1
BEGIN
INSERT INTO LogTable VALUES (ROUND((RAND()* 1000000),0), SPACE(1024))
SELECT @rows+=1
SELECT @cnt = (size * 1.0 * 8.0)/1024.0
FROM Test.sys.database_files
WHERE data_space_id = 0
AND [FILE_ID]=5
IF @cnt>1.0
BREAK
END
SELECT @rows;
GO
I randomly choose [FILE_ID]=5, but I already know that I could have used 3 or 4 as well. When the script complete
s, I inserted a total of 23461 rows, but yours will most likely vary. Notice now the size of my files when I run the first script:
Interestingly, all of the log files have been written to – obviously the first tlog file has most of the writes, but the others are the same size by the time the script hit the BREAK. Apparently SQL Server did write to more than one log file, but it’s strange that it wrote only 1MB to 32MB for the first transaction log that was created. Subsequent runs indicate a similar behavior, but I ran a few times and found that it hit the BREAK with considerably fewer rows than 23K from the first run.
And then there's Tom Smykowski... He's useless.
What is SQL Server doing here, and what’s in these additional tlog files? I can only answer the second question, and may have to ping the storage engine team to explain it I suppose. In order to find out what’s going, I went to ApexSQL and downloaded the ApexSQL Log reader tool; go download the 14-day trial and check it out if you are following along with me. When I consulted at Rent-A-Center a few years ago by the way, they had the Apex suite of tools and I really liked them.
I installed and fired up the tool and here’s what I get when I have it inspect the Test_Log2, 3, and 4.ldf files:
I get nothin! So obviously SQL Server is writing some internals stuff here and not info from our INSERT statement(s). Cool! Running the tool with only that first log file, I get this, so we know that the tool is working correctly:
And there’s IO as well for those log files for disk drives e, f, and g…as we would expect:
Conclusion
We’ve seen that 1) SQL Server does not round-robin multiple transaction log files, 2) additional files do get written to, but not with transactional information per-se, 3) whatever is getting written to these tlog files happens at the same time, and is only a fraction of what is written to the first tlog file created for the database, and 4) there is I/O present on the disk when the additional tlog files are written to, but not much.
I’m going to keep looking for some answers here, so let’s call this part 1 and I’ll investigate in some more and see what I can find…definitely interesting to me, and I hope you enjoyed the read. Right now I’m fairly convinced at this point that additional transaction log files are of no use in a normal Microsoft SQL Server database setup.
Thanks much,
Lee Everest
-------------------------
I can't believe what a bunch of nerds we are. We're looking up "money laundering" in a dictionary
References
[1] Unnamed resource from Microsoft haha
[2] http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx
[3] http://www.sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx
[4] http://www.apexsql.com/default.aspx