Does SQL Server Now Distribute Transactions Evenly Across Multiple Log Files?

It is generally known throughout the SQL Server world that having multiple log files on a database is not a good idea. While multiple data files spread across multiple drives will usually give you a performance boost, the same does not hold true for database log files.

I have always heard that if your database does have multiple log files, the first file is used in its entirety, then the second file will be used. And while that may have been the case in earlier versions of SQL Server, I stumbled upon something recently in SQL Server 2016 that makes wonder if that’s still how things work.

But first….

Why I Had To Add Multiple Transaction Log Files

We ran out of hard drive space on the log drive. While it was an unusual circumstance that caused this, that’s basically what happened.

We have an Availability Group with multi-terabyte databases replicating between data centers in different parts of the country. Our main connection between the two data centers went down, so we were running on the slower backup connection. Much slower. So much slower that it was not able to keep up pace with our AG. At one point our Secondaries were 8 hours behind our Primaries. So, as time went on this bottleneck caused our t-log files to grow pretty large. As we had a few of them getting into the 800-900 GB range, we began to run out of hard drive space. In order to prevent that, I decided to add additional log files to the databases, putting them on a different drive, thereby allowing them to keep growing as needed. We then turned off auto-growth on the original log files to prevent them from further filling up the drive, and (in our minds) forcing SQL to shift to using the second log file once the first one filled up.

Something Strange Happened

When the new log files were added, SQL Server immediately filled them to match the fullness percentage of the original log files. As I manually increased the size of the log file, SQL Server again shifted the transactions around to keep both log files at the exact same fullness percentage. So, if the first log was 95{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} full, the second log was 95{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} full, regardless of the actual log file sizes. If the first log was 80{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} full, so was the second. The more I expanded the second log, the more transactions SQL Server would move to it, always keeping the fullness percentage the same on both log files. The larger the second log became, the more space it freed up on the first log, but the 2 logs were always exactly in sync in terms of fullness.

Here’s a screenshot of what I’m talking about. This was taken after everything was resolved and the Primay logs had cleared out and been resized (and before deleting the second log)

The reason this seems so odd to me is that I’ve always been under the impression that if your database has multiple log files, SQL Server will use the first file in its entirety, and only when it is full will it begin using the second log file. I was expecting to see the primary log stay at 100{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} fullness while the secondary began to fill up and grow. Apparently that is not the case.

Admittedly, with everything I had going on I did not investigate this much past this quick observation, so I would love to hear from someone that may have some deeper insight into this. Was my understanding of this incorrect all along, or is this something that has changed in recent versions of SQL Server?