Future Proof Your SQL Server Transaction Log Files For Instant File Initialization?

Finger Snap

---

Look, I don’t blame you if you haven’t dug deep into what SQL Server 2022 has to offer just yet. It’s hard enough to keep up with all the problems fixed and caused by cumulative updates.

One thing you may want to pay attention to is how transaction log files are grown and VLFs are created, especially for new databases.

First, all new databases created on SQL Server 2022 have autogrowth set to 64MB. This allows for two things:

1. Those growths can take advantage of instant file initialization
2. That 64MB growth increment will create a single VLF

Now, you might be excited about log files getting instant file initialization. But it only works for those small growths. Perhaps only in Azure would a growth of 64MB not be instant anyway.

I don’t know anyone else who spends a lot of time waiting for database growths of 64MB to finish.

Transaction log files cannot be initialized instantaneously, however, starting with SQL Server 2022 (16.x), instant file initialization can benefit transaction log autogrowth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization.

Other changes to VLF creation are something along the lines of:

• <= 64MB: 1 VLF
• >64MB and <=1GB: 8 VLFs
• > 1GB: 16VLFs

If your log file autogrowth settings are larger than 1GB, either because you set them that way, or because your files are growing by a percentage, you may end up with some rather large VLFs.

To see what your current settings are, run this query:

SELECT
    database_name = 
        DB_NAME(mf.database_id),    
    logical_name = 
        mf.name,
    file_name = 
        mf.physical_name,
    size_gb = 
        (mf.size * 8) / 1024 / 1024,
    max_size_gb =
        CONVERT
        (
            bigint,
            CASE
                WHEN mf.max_size = -1
                THEN 0
                ELSE (mf.max_size * 8.) / 1024 / 1024
            END
        ),
    autogrowth_mb = 
        CASE 
            WHEN mf.is_percent_growth = 1
            THEN RTRIM(mf.growth) + N'%'
            WHEN (mf.growth * 8 / 1024) < 1024
            THEN RTRIM((mf.growth * 8) / 1024) + ' MB'
            WHEN (mf.growth * 8 / 1024) >= 1024
            THEN RTRIM((mf.growth * 8) / 1024 / 1024) + ' GB'
         END,
    usage = 
        CASE
            WHEN mf.type = 0
            THEN 'data'
            WHEN mf.type = 1
            THEN 'log'
            WHEN mf.type = 2
            THEN 'filestream'
            WHEN mf.type = 3
            THEN 'nope'
            WHEN mf.type = 4
            THEN 'fulltext'
        END
FROM sys.master_files AS mf
WHERE mf.database_id > 4
AND   mf.type = 1
ORDER BY
    mf.database_id,
    mf.type,
    mf.file_id
OPTION(RECOMPILE);

 

If you’re planning on moving to SQL Server 2022, now’s the time to make adjustments so you’re not caught off guard.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.