New VLF status value

At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes:

  • 0 = the VLF is not active (i.e. it can be (re)activated and overwritten)
  • (1 = not used and no-one seems to remember what it used to mean)
  • 2 = the VLF is active because at least one log record in it is ‘required’ by SQL Server for some reason (e.g. hasn’t been backed up by a log backup or scanned by replication)

A few weeks ago I learned about a new VLF status code that was added back in SQL Server 2012 but hasn’t come to light until recently (at least I’ve never encountered it in the wild). I went back-and-forth with a friend from Microsoft (Sean Gallardy, a PFE and MCM down in Tampa) who was able to dig around in the code to figure out when it’s used.

It can show up on an Availability Group secondary replica (only, not on the primary, and isn’t used in Database Mirroring):

  • 4 = the VLF exists on the primary replica but doesn’t really exist yet on this secondary replica

The main case where this status can happen is when a log file growth (or creation of an extra log file) has occurred on the primary replica but it hasn’t yet been replayed on the secondary replica. More log records are generated on the primary, written to a newly-created VLF, and sent across to the secondary replica to be hardened (written to the replica’s log file). If the secondary hasn’t yet replayed the log growth, the VLFs that should contain the just-received log records don’t exist yet, so they’re kind of temporarily created with a status of 4 so that the log records from the primary can be hardened successfully on the secondary. Eventually the log growth is replayed on the secondary and the temporary VLFs are fixed up correctly and change to a status of 2.

It makes perfect sense that a scheme like this exists, but I’d never really thought about this case or experimented to see what happens.

Anyway, now you know what status 4 means if you ever see it (and thanks Sean!)

14 thoughts on “New VLF status value

  1. I wonder if this would have helped me recently. We had a new DB created for a datawarehouse, and the initial index rebuild kicked off, an dof course blew up the log drive. Our AG backs up on the secondaries, and even though the log was mostly empty, I was getting LOG BACKUP in log_reuse_wait_desc. Digging around, I eventually found (without having to take this large database out of the AG), that I needed to backup the log on the primary. Once completed, I was able to shrink the log (and of course asked that the drive be increased significantly).

  2. I remember in Sybase we had the syslogshold table with the column reserved, I’m not quite sure but 0 is for unused and 1 for used, maybe the status of vlf could be sort of entail from there.

  3. Paul Sir,

    I always thought that log records/blocks are always played in order on secondary but this is different case where VLF (owner) is created Ist on primary but later on Secondary and its tenant (log records) are created Iind on primary but on secondary they are created Ist

    1) VLF A creation on primary
    2) log record1 on Primary
    3) Log record1 played/redo’d on Secondary
    4) VLF A created on Secondry

    Please sir is it correct

  4. Paul Sir,
    after exec dbcc loginfo. The result has one column as status . But I can’t found which files record the status? Did you know that?

    1. I think you’re asking where the VLF statuses are stored. Each VLF has a header with metadata that includes the status. You can see these in memory using DBCC DBTABLE (after enabling trace flag 3604) and scroll down to the entries starting with lfcb.

      1. By looking at the LDF file, after the status field is stored in the seqno field in the VLF header structure, you can see that the status field is always 0. What is the reason? If this field is always 0, how to determine which VLF file is active in MSSQL? Is it through chekpoint LSN?

        1. What are you talking about opening the LDF file? You see which VLFs are active from DBCC LOGINFO, the new dMV, or the lfcbs in the DBCC DBTABLE output. If you don’t see it, you’re missing one.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.