Sql-server – Why is the transaction log not truly circular

sql servertransaction-log

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:

enter image description here

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:

enter image description here

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:

enter image description here

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://docs.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:

enter image description here

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.

Best Answer

In the face of log file growth events and / or large, long-running transactions, the order of the output of DBCC LOGINFO can look at a little wacky - temporarily, at least.

The important thing to remember is that the FSeqNo is the logical order of the VLFs.

Paul Randal talks about this in his blog post Inside the Storage Engine: More on the circular nature of the log (emphasis mine):

Once the active transaction I created commits or rolls back, VLFs 3, 4, 1, and 2 can clear and then the ‘normal’ sequencing of VLFs in the log will resume.

Every VLF has a small header which contains the sequence number of the VLF within the transaction log, so the log can kind of do contortions to work around active VLFs in the middle of the log.

These irregularities should self-correct over time as VLFs clear normally.

Related Question