Setting a Fixed Size for Transaction Log Virtual Log Files (VLFs)

The documentation has this to say about virtual log file (VLF) sizes:

The SQL Server Database Engine divides each physical log file internally into several virtual log files (VLFs). Virtual log files have no fixed size, and there’s no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it’s creating or extending log files. The Database Engine tries to maintain a few virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files can’t be configured or set by administrators.

It then goes on to describe the problems having too many VLFs can cause, and how the database owner can arrange things so a reasonable number of VLFs are created. There’s even a (mostly accurate) formula for the number and size of VLFs SQL Server will create when asked to extend a transaction log file.

This is all very familiar, of course, but it is also dumb. Why on earth should we have to worry about internal formulas? It seems ridiculous to have to provision or grow a transaction log in pieces just to get a reasonable VLF outcome.

Wouldn’t it be better to be able to specify a fixed size for VLFs instead?

Starting with SQL Server 2022, there is now a way though it is undocumented and unsupported for the time being at least.

You can’t use it in a production database and there’s a real risk of it damaging your database beyond repair. Aside from those warnings, there’s no reason not to play around with it in a development environment. Or, if you’re simply curious to know more, read on.

sp_start_fixed_vlf

This undocumented special stored procedure is provided to transition a database to using fixed size VLFs. It takes two unnamed parameters, the database name and desired VLF size in MB. Tested on SQL Server 2022 CU10 Developer Edition and Express Edition.

Demo

Let’s create a disposable database:

USE master;
GO
DROP DATABASE IF EXISTS LogTest;
GO
CREATE DATABASE LogTest 
ON PRIMARY 
(
    NAME = LogTestData, 
    FILENAME = 'D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.mdf'
)
LOG ON 
(
    NAME = LogTestLog, 
    FILENAME = 'D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf', 
    SIZE = 1MB,
    FILEGROWTH = 0MB,
    MAXSIZE = UNLIMITED
);
GO
-- Query Store is on by default but we won't be needing it
ALTER DATABASE LogTest SET QUERY_STORE = OFF (FORCED);
ALTER DATABASE LogTest SET QUERY_STORE CLEAR ALL;

Some things to note here: I’ve created the database with a 1MB log file, auto-growth turned off, and the maximum size of the log set to UNLIMITED. This will make more sense later on.

The VLFs currently look like this:

SELECT
    vlf_begin_offset,
    vlf_size_mb,
    vlf_sequence_number,
    vlf_active,
    vlf_status,
    vlf_parity,
    vlf_first_lsn,
    vlf_create_lsn
FROM sys.dm_db_log_info(DB_ID(N'LogTest'));

It will also be helpful to get some feedback on what we’re doing, which will be provided by messages returned when trace flag 3004 is enabled:

DBCC TRACEON (3004, 3604);

Preparing to set the VLF size

The first thing is to choose the size of VLFs we want. I’m going to choose 64MB since that’s the largest log file size SQL Server 2022 can create using instant file initialization. You might choose a smaller or larger size, depending on the expected maximum size of the log and how many VLFs you can tolerate.

For reasons that will again become clear shortly, a desirable first step is to expand the current size of the transaction log to a multiple of the desired fixed VLF size. In this case, I’m simply going to grow the log to 64MB:

ALTER DATABASE LogTest 
MODIFY FILE (NAME = LogTestLog, SIZE = 64MB);

The messages tab of SSMS confirms we used instant file initialization:

Skip zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf 
from page 128 to 8192 (Offset 0x100000 to 0x4000000) 63 mb

The VLFs are now:

SQL Server created a single new VLF of size 63MB, despite the documentation indicating we should expect 8 new VLFs:

Virtual log file (VLF) creation follows this method:

• In SQL Server 2014 (12.x) and later versions, if the next growth is less than 1/8 of the current log physical size, then create 1 VLF that covers the growth size.
• If the next growth is more than 1/8 of the current log size, use the pre-2014 method, namely:
• If growth is less than 64 MB, create 4 VLFs that cover the growth size (for example, for 1-MB growth, create 4 VLFs of size 256 KB).
  • In Azure SQL Database, and starting with SQL Server 2022 (16.x) (all editions), the logic is slightly different. If the growth is less than or equal to 64 MB, the Database Engine creates only one VLF to cover the growth size.
• If growth is from 64 MB up to 1 GB, create 8 VLFs that cover the growth size (for example, for 512-MB growth, create 8 VLFs of size 64 MB).
• If growth is larger than 1 GB, create 16 VLFs that cover the growth size for example, for 8-GB growth, create 16 VLFs of size 512 MB).

Transitioning to fixed VLFs

We now call the special procedure to set the desired fixed size VLFs. The parameters are unnamed, so you can call them anything you like if the mood takes you. The maximum value for the VLF size parameter is 16,384MB (16GB).

EXECUTE sys.sp_start_fixed_vlf 
    @quack = N'LogTest',    -- database name
    @oink = 64;             -- fixed VLF size in MB

You may receive an error message the first time you try this:

Msg 9050, Level 16, State 4, Procedure sys.sp_start_fixed_vlf
Fixed VLF transition was rejected.

It will succeed when you try it a second time.

When successful, the messages tab shows the progress of the operation:

StartVlfStateTransition[10]: vifSize(MB) passed 64 
  spaceLeft in current VLF 66052096 bytes, number of drain log used 23
Skip zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf 
  from page 8192 to 16384 (Offset 0x4000000 to 0x8000000) 64 mb
TryTransitionToFixedVlfState[10]: 
  in mixed VLF state,
  hold up by ACTIVE_TRANSACTION at VLF 39 activeVlf 39, startFixedVLF 0
Skip zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf 
  from page 16384 to 24576 (Offset 0x8000000 to 0xc000000) 64 mb
StartVlfStateTransition[10]: extra LogNoop [6000] 
  start VLF [43], end VLF [44], fixed VLF offset 67108864
TryTransitionToFixedVlfState[10]: 
  in mixed VLF state,
  hold up by CHECKPOINT at VLF 39 activeVlf 39, startFixedVLF 44
TryTransitionToFixedVlfState[10]: 
  in mixed VLF state,
  hold up by CHECKPOINT at VLF 39 activeVlf 39, startFixedVLF 44
TryTransitionToFixedVlfState[10]: 
  transitioned to fixed VLF state at VLF [44]

To summarise, SQL Server:

1. Created two new VLFs of size 64MB using instant file initialization.
2. Generated 6,000 LOP_NULL log entries to move the active log into a new VLF.
3. Issued internal checkpoints as necessary.

After the transition

The VLFs are now:

Two things to notice:

1. There are two new 64MB VLFs. The first one is active and the second one is so far unused.
2. The previous, non-fixed VLFs have a new VLF status of 8 (meaning deprecated). These VLFs will not be used in future (unless we transition back out of using fixed VLFs).

Side Effects

Using fixed size VLFs comes with a number of behavioural changes and restrictions. Some of these may be product defects, others deliberate.

Size

The total size of the log is now reported as 192MB although only 128MB is usable. The missing 64MB is in the deprecated VLFs.

Shrinking is ignored

While the transaction log is in fixed VLF mode, DBCC SHRINKFILE is ignored. An informative message only appears with trace flag 3004 active:

USE LogTest;
DBCC SHRINKFILE (LogTestLog, 128);

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
10	2	24576	128	24576	128

Disable log on db [10] shrink under HS migration.

(1 row affected)
DBCC execution completed. 
If DBCC printed error messages, contact your system administrator.

The message suggests this special procedure is intended for use in migrating to HS (which I suppose means the Azure Hyperscale service tier).

It wouldn’t surprise me if Microsoft were already using fixed size VLFs in their managed databases to avoid long recovery times and the other issues people have been complaining about for a decade.

The log will auto-grow, regardless

The transaction log will add new fixed size VLFs as needed, even if the log is set not to auto-grow. Remember, I set the log not to auto-grow originally.

Let’s create a table and add some rows to it to demonstrate:

USE LogTest;
GO
CREATE TABLE dbo.BigRows 
(
    c1 integer IDENTITY, 
    c2 CHAR (8000) NOT NULL
);
GO
INSERT TOP (20 * 1000) dbo.BigRows 
    WITH (TABLOCKX) 
    (c2)
SELECT 'x' 
FROM master.dbo.spt_values AS SV1
CROSS JOIN master.dbo.spt_values AS SV2;

Two new 64MB VLFs have been added:

Remember, this happened despite setting the transaction log not to auto-grow.

Maximum log size

Do not set a maximum size for the transaction log

If the log needs to grow beyond any limit you set, you will be rewarded with a retail assertion, a broken connection, server dump, and a database that will not restart.

This is the reason I set MAXSIZE = UNLIMITED.

I was always able to recover the database eventually when I encountered this, but I do not recommend the experience.

Growing the log manually

You are still able to extend the log file manually, but the new total size must include the deprecated VLFs and be a multiple of the fixed VLF size. This is why I initially expanded the log. It makes future growths easier, so you don’t have to remember to factor in whatever the original size was.

In the running example, the total log file size is currently 320MB (5 x 64MB).

The first 64MB is unusable deprecated VLFs. Then we have the two original 64MB VLFs added by the transitioning process and two more 64MB VLFs added when we added 20,000 rows to the table.

Attempting to extend the log manually to a size that is not a multiple of the fixed VLF size does not go well:

ALTER DATABASE LogTest 
MODIFY FILE (NAME = LogTestLog, SIZE = 400MB);

Zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf 
  from page 40960 to 51200 (Offset 0x14000000 to 0x19000000) 80 mb
Zeroing completed on D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf 
  (elapsed = 53 ms) 80 mb
  
Location:	 "sql\\ntdbms\\storeng\\dfs\\trans\\logmgr.cpp":24079
Expression:	 fcb->GetFixedVlfSizeHSMigration() > 0 && 
  logFileSize == (fcb->GetFixedVlfSizeHSMigration() * 1024 * 1024)
SPID:		 69
Process ID:	 992
Thread ID:	 30412

Msg 3624, Level 20, State 1, Line 64
A system assertion check has failed. 
Check the SQL Server error log for details. 
Typically, an assertion failure is caused by a software bug 
  or data corruption. 
To check for database corruption, consider running DBCC CHECKDB.
If you agreed to send dumps to Microsoft during setup, 
a mini dump will be sent to Microsoft. 
An update might be available from Microsoft in the latest
  Service Pack or in a Hotfix from Technical Support.
Msg 596, Level 21, State 1, Line 63
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 63
A severe error occurred on the current command.  
The results, if any, should be discarded.

If you try this, reconnect, and enable the informative trace flags again:

DBCC TRACEON (3004, 3604);

The log file will still be 320MB. We can successfully extend it to a multiple of the fixed VLF size:

ALTER DATABASE LogTest 
MODIFY FILE (NAME = LogTestLog, SIZE = 640MB);

Zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf 
  from page 40960 to 81920 (Offset 0x14000000 to 0x28000000) 320 mb
Zeroing completed on D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf 
  (elapsed = 151 ms) 320 mb

The VLFs are now:

The expansion created five new 64MB VLFs. Instant file initialization could not be used because the total expansion of 320MB exceeded the limit of 64MB.

Turning Fixed Size VLFs Off

To transition the database back to normal, run the special procedure again with a fixed VLF size of zero:

EXECUTE sys.sp_start_fixed_vlf 
    @quack = N'LogTest', 
    @oink = 0;

StartVlfStateTransition[10]: vifSize(MB) passed 0 
  spaceLeft in current VLF 24092672 bytes, number of drain log used 0

Then issue a CHECKPOINT:

USE LogTest;
CHECKPOINT;

ClearVLFDeprecateStatus at LSN 2e:1483a:1 Start Log 2e:14840:99

The deprecated (status 8) VLFs are returned to active service:

Back to normal

The log can now be shrunk or expanded as usual:

USE LogTest;
DBCC SHRINKFILE (LogTestLog, 128);

Tidy up

Drop the database when you have finished experimenting and turn the trace flags off:

USE master;
DROP DATABASE LogTest;
DBCC TRACEOFF (3004, 3604);

Conclusion

The special procedure sys.sp_start_fixed_vlf may be included in SQL Server 2022 by accident, or only to facilitate migration to the cloud. Microsoft have said nothing about it, as far as I am aware, so all we can do is speculate.

Still, it would be nice if fixed size VLFs became a documented feature in a future release of SQL Server with proper language support.

As you can see from some of the examples above, the current implementation certainly isn’t finished at the moment, though it is minimally functional. To repeat the warning from earlier, this is not for production use or use on any database or instance you care about.