5

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

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.

Improve this question
2
  • 1
    have you checked this blog post from Paul Randal - sqlskills.com/blogs/paul/… ?
    – Kin Shah
    Commented Mar 23, 2020 at 14:36
  • Not exactly what is was looking for, but very interesting. Very much thanks for your help.
    – Zitronent
    Commented Mar 23, 2020 at 17:51

1 Answer 1

Reset to default
6

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.