Paul Randal points out a new VLF status which can appear if you’re using an Availability Group:
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.
Read on to uncover the mysteries of the VLF status of 4.