I did a little bit of hands-on research recently about how the transaction log in sql server works. I came to a problem that I can't get my head around.
First I created a new test database with full recovery mode and created an empty test table in it. Then I did an initial full backup and also afterwards a first tlog backup. This resulted in the following DBCC LOGINFO output:
So far so good. Afterwards I inserted several rows, to make sure the first three VLFs are in use. As my next step I did another tlog backup and manual checkpoint, so that the first two VLFs were truncated which resulted in this DBCC LOGINFO:
Then I added more rows, so that the transaction log wrapped around and the database engine used the first VLF again. In addition to that I added more and more rows and finally the second VLF was full. Since the third VLF was not yet truncated the transaction log grew and four additional VLFs were added. After all of this I did the next transaction log backup and also a manual checkpoint, which led finally to this:
Now what bothers me is what happened next. I thought that when I make so many changes, that the last VLF (here in row 8) will become full, the log will wrap around again and will start to write to the first VLF again (here with FSeqNo 41), since this VLF is already truncated. I expected this, because in " https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15 " they suggest, that the transaction log is of a circular nature. But what really happened is, that the database engine wrote to the next VLF with the lowest FSeqNo:
Why is this behavior contrary to the Microsoft docs or, didm I just not understand the it right? Any help or explanation would be appreciated.