SQL Server 2022 Hidden Gem: Instant File Initialization Now Works with TDE on Transaction Logs

A DBA on a TDE-enabled environment asks to enable Instant File Initialization for TempDB. The response comes back: TDE prevents IFI from working, so enabling it will not help. The conversation ends there and the team goes looking elsewhere for solutions to slow file growth events.

The response is partially correct, partially outdated, and missing the most important detail. TDE does block IFI for data files and has always done so for a documented security reason. But since SQL Server 2022, transaction log autogrowth events up to 64 MB benefit from IFI automatically, even on TDE-enabled databases, even without the SE_MANAGE_VOLUME_NAME privilege. That is a meaningful change that most DBAs in TDE environments do not know about because it shipped quietly as one of the SQL Server 2022 hidden gems.

This article explains exactly what IFI is, precisely where TDE blocks it and where it does not, what SQL Server 2022 changed and why, what the 64 MB boundary means in practice, and what the correct answer is when TempDB data file autogrowths are causing stalls in a TDE environment. All behavior described is verified against Microsoft Learn documentation as of July 2026.

1 What Instant File Initialization Is and Why It Matters Beginner

When SQL Server allocates new space for a database file during creation or during an autogrowth event, the default behavior is to zero out the newly allocated disk space before making it available. Every byte in the new allocation is overwritten with zeros. This zeroing operation takes time proportional to the size of the allocation. A 1 GB autogrowth event means zeroing 1 GB of disk before the application can continue. Sessions that triggered the growth wait for it to complete.

Instant File Initialization eliminates the zeroing step for eligible files. When IFI is active, SQL Server calls the Windows SetFileValidData API to extend the file without zeroing the new space. The disk space is claimed immediately and SQL Server marks the region as valid. An autogrowth event that previously took seconds drops to milliseconds. The physical disk sectors beneath may contain residual data from previously deleted files, but SQL Server manages its own page allocation and will not allow reads of pages it has not written, so this is safe from a database integrity perspective.

2 Why File Zeroing Causes Performance Stalls Beginner

The zeroing operation runs synchronously from the perspective of the session that triggered it. When a user transaction causes a data file or log file to autogrow, that session waits for the zeroing to complete before the write that triggered the growth can proceed. On a busy OLTP system this manifests as periodic pauses in application responsiveness that are difficult to correlate with any specific query because the wait happens at the storage layer below any query execution.

The SQL Server error log records every autogrowth event with its duration. A pattern of frequent short-duration events indicates that autogrowth settings are too small and events are triggering constantly. A pattern of infrequent but long-duration events indicates that the file is growing in large increments with significant zeroing stalls each time. Both patterns point to the same root cause: the file was not sized correctly and is autograding when it should not be. IFI reduces the stall duration for eligible files but does not eliminate the underlying sizing problem.

-- Check the SQL Server error log for autogrowth events
-- Look for "Autogrow" messages with duration in milliseconds
EXEC xp_readerrorlog 0, 1, N'Autogrow';

-- Also check the previous log files for historical patterns
EXEC xp_readerrorlog 1, 1, N'Autogrow';
EXEC xp_readerrorlog 2, 1, N'Autogrow';

3 Why TDE Blocks IFI for Data Files Intermediate

Transparent Data Encryption encrypts data at the page level. Every page written to a data file is encrypted before it leaves the buffer pool. Every page read from a data file is decrypted when it enters the buffer pool. The encryption is transparent to the application.

IFI is incompatible with TDE for data files because of what IFI leaves on disk. When IFI allocates new data file space without zeroing, the new disk sectors may contain residual data from previously deleted files. In a non-TDE database this is safe because SQL Server will overwrite those sectors with actual encrypted or unencrypted database pages before allowing any read. In a TDE database, those sectors exist within an encrypted file. An attacker with raw disk access could potentially find unencrypted residual data from other files mixed into an otherwise encrypted database file, breaking the at-rest encryption guarantee.

Microsoft therefore blocks IFI for data files whenever TDE is enabled. According to Microsoft Learn documentation, when database files are created, instant file initialization is unavailable when TDE is enabled. This applies to initial database creation and to all data file autogrowth events in all SQL Server versions. No configuration change or trace flag enables IFI for TDE data files. The block is by design and permanent for data files.

4 What SQL Server 2022 Changed for Transaction Log IFI Intermediate

Before SQL Server 2022, IFI did not apply to transaction log files in any version. Log files were always zeroed on creation and on every autogrowth event. The zeroing was required because of how SQL Server reads the log during crash recovery: it scans forward through log records until it finds zeros, which mark the end of valid log data. Without zeroing, SQL Server could not reliably detect the end of the log during recovery.

SQL Server 2022 introduced IFI for transaction log autogrowth events up to 64 MB. The technical reason this became possible is that the transaction log is written in a strictly serial fashion. SQL Server always knows exactly where the last valid log record ends because it wrote it. The serial write pattern provides the same boundary guarantee that zeroing previously provided, making the zeroing unnecessary for the log file in the same way it is unnecessary for data files when IFI is enabled.

According to Microsoft Learn documentation, unlike instant file initialization for data files which is prevented if TDE is enabled, instant file initialization is allowed for transaction log growth on databases that have TDE enabled, because of how the transaction log file grows and the fact that the transaction log is written into in a serial fashion. The security concern about residual data in uninitialized space does not apply to the log file in the same way it applies to randomly-accessed data file pages.

Microsoft also confirmed this on the official Data Exposed show with Bob Ward and Anna Hoffman, which covered SQL Server 2022 hidden gems including this specific IFI log file behavior.

5 The 64 MB Boundary: What It Means in Practice Intermediate

The IFI benefit for transaction log growth in SQL Server 2022 applies only to autogrowth events of 64 MB or smaller. Log autogrowth events larger than 64 MB still require zeroing and experience the associated stall. This boundary is significant for two practical reasons.

First, Microsoft set the default autogrowth size for transaction log files to 64 MB for new databases in SQL Server 2022, deliberately aligning the default with the IFI ceiling. A database created on SQL Server 2022 with default settings has its log file configured to autogrow in 64 MB increments, which means every autogrowth event falls within the IFI range automatically. This alignment is intentional.

Second, many existing databases carry legacy autogrowth settings that predate SQL Server 2022 and may use percentage-based growth or fixed sizes larger than 64 MB. For those databases, any autogrowth event that exceeds 64 MB still stalls. The fix is to set transaction log autogrowth to 64 MB fixed increments on SQL Server 2022 instances to ensure every autogrowth event qualifies for IFI.

-- Check all database file autogrowth settings
-- Identify log files where growth exceeds the 64 MB IFI ceiling
-- Run against master on the SQL Server instance

SELECT
    d.name                                      AS DatabaseName,
    f.name                                      AS LogicalName,
    f.type_desc                                 AS FileType,
    CAST(f.size * 8.0 / 1024 AS DECIMAL(10,1)) AS CurrentSizeMB,
    CASE f.is_percent_growth
        WHEN 1 THEN CAST(f.growth AS VARCHAR) + N'%'
        ELSE CAST(f.growth * 8 / 1024 AS VARCHAR) + N' MB'
    END                                         AS GrowthSetting,
    CASE
        WHEN f.type_desc = N'ROWS'
            THEN N'Data file: IFI blocked by TDE (all SQL Server versions)'
        WHEN f.is_percent_growth = 1
            THEN N'WARNING: Percentage growth exceeds 64 MB IFI ceiling'
        WHEN f.type_desc = N'LOG'
         AND f.is_percent_growth = 0
         AND f.growth * 8 / 1024 > 64
            THEN N'WARNING: Log growth exceeds 64 MB IFI ceiling'
        WHEN f.type_desc = N'LOG'
         AND f.is_percent_growth = 0
         AND f.growth * 8 / 1024 <= 64
            THEN N'OK: Log growth within 64 MB IFI range'
        ELSE N'Review'
    END                                         AS IFIAssessment
FROM sys.master_files   f
JOIN sys.databases      d ON f.database_id = d.database_id
WHERE d.database_id > 4
ORDER BY d.name, f.type_desc DESC, f.file_id;

6 The TempDB Scenario: When IFI Is Not the Answer Intermediate

The conversation that prompted this article: TempDB data files are not sized correctly and are autograding under load. The suggestion is to enable IFI. The response is that TDE blocks IFI. Both sides are addressing the wrong problem.

TempDB data file autogrowth events in any TDE environment cannot benefit from IFI in any SQL Server version. TDE blocks IFI for all data files. The SQL Server 2022 log IFI improvement does not apply to data files. So the colleague saying TDE blocks IFI for TempDB data files is correct.

However, the correct conclusion is not that nothing can be done. The correct conclusion is that IFI for data files is not the solution to TempDB autogrowth stalls when TDE is enabled. The correct solution is to eliminate the autogrowth events by pre-sizing TempDB correctly. TempDB is recreated from the configured file sizes every time SQL Server restarts. Pre-sizing TempDB means autogrowth events never occur during normal operation because the files are already large enough.

-- Find the right TempDB size by measuring peak usage
-- Run this query hourly during normal and peak periods for one to two weeks
-- The target size per data file is: peak (UserObjects + InternalObjects) / number of data files
-- Plus 25 percent headroom

USE tempdb;
SELECT
    SUM(unallocated_extent_page_count)   * 8 / 1024 AS FreeSpaceMB,
    SUM(user_object_reserved_page_count) * 8 / 1024 AS UserObjectsMB,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS InternalObjectsMB,
    SUM(version_store_reserved_page_count)   * 8 / 1024 AS VersionStoreMB
FROM sys.dm_db_file_space_usage;

-- After determining the right size, configure TempDB files permanently
-- All data files must be equal sizes for proportional fill to work correctly
-- Adjust sizes based on observed peak usage

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp2,   SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp3,   SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp4,   SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 512MB,  FILEGROWTH = 64MB);
-- Log growth at exactly 64 MB benefits from SQL Server 2022 log IFI even with TDE

-- Verify the number of TempDB data files
-- Should equal the number of logical processor cores up to 8
SELECT name, physical_name, size * 8 / 1024 AS SizeMB
FROM tempdb.sys.database_files ORDER BY type, file_id;

7 Checking Whether IFI Is Active on the Current Instance Beginner

-- Method 1: Check the SQL Server error log for IFI status at startup
-- SQL Server writes one of these messages every time it starts
EXEC xp_readerrorlog 0, 1, N'Instant File Initialization';
-- "Database Instant File Initialization: enabled." = SE_MANAGE_VOLUME_NAME is granted
-- "Database Instant File Initialization: disabled." = privilege not granted
-- NOTE: Even if disabled, SQL Server 2022 log IFI still works automatically

-- Method 2: Check through sys.dm_server_services
SELECT
    servicename,
    service_account,
    instant_file_initialization_enabled   -- Y or N
FROM sys.dm_server_services
WHERE servicename LIKE N'SQL Server (%';

-- Method 3: Check TDE status across all user databases
SELECT
    d.name                          AS DatabaseName,
    CASE dek.encryption_state
        WHEN 3 THEN N'TDE ON: data file IFI blocked'
        WHEN 1 THEN N'Not encrypted'
        WHEN NULL THEN N'No encryption key'
        ELSE N'Encryption state: ' + CAST(dek.encryption_state AS VARCHAR)
    END                             AS TDEStatus
FROM sys.databases                  d
LEFT JOIN sys.dm_database_encryption_keys dek ON d.database_id = dek.database_id
WHERE d.database_id > 4
ORDER BY d.name;

8 Extended Events: Capturing File Growth Events Intermediate

Extended Events are the most precise way to measure file autogrowth stall duration. The session below captures every file growth event with its duration in microseconds, which allows direct before-and-after comparison when changing autogrowth settings or upgrading to SQL Server 2022.

-- Create an Extended Events session to capture file autogrowth events
-- with precise duration measurement

CREATE EVENT SESSION [Capture_FileGrowth] ON SERVER
ADD EVENT sqlserver.database_file_size_change
(
    ACTION
    (
        sqlserver.database_name,
        sqlserver.session_id
    )
    WHERE sqlserver.is_system = 0
)
ADD TARGET package0.ring_buffer
(
    SET max_memory = 51200
)
WITH
(
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY      = OFF
);

ALTER EVENT SESSION [Capture_FileGrowth] ON SERVER STATE = START;

-- Query results from the ring buffer
SELECT
    event_data.value('(event/@timestamp)[1]',    'DATETIME2')       AS EventTime,
    event_data.value('(event/action[@name="database_name"]/value)[1]','NVARCHAR(128)') AS DatabaseName,
    event_data.value('(event/data[@name="file_type"]/text)[1]',  'NVARCHAR(50)')  AS FileType,
    event_data.value('(event/data[@name="size_change_kb"]/value)[1]','BIGINT') / 1024 AS SizeChangeMB,
    event_data.value('(event/data[@name="duration"]/value)[1]',  'BIGINT') / 1000     AS DurationMs
FROM
(
    SELECT CAST(target_data AS XML) AS ring_buffer_data
    FROM sys.dm_xe_sessions         s
    JOIN sys.dm_xe_session_targets  t ON s.address = t.event_session_address
    WHERE s.name        = 'Capture_FileGrowth'
      AND t.target_name = 'ring_buffer'
) AS rb
CROSS APPLY ring_buffer_data.nodes('//RingBufferTarget/event') AS xevents(event_data)
ORDER BY EventTime DESC;

-- On SQL Server 2022 with TDE: log growth events up to 64 MB = DurationMs in single digits
-- Pre-2022 or growth over 64 MB: DurationMs in hundreds or thousands

9 Recommended Autogrowth Settings for SQL Server 2022 Intermediate

Autogrowth is a safety net, not a sizing strategy. Files should be sized large enough that autogrowth events are rare. When they do occur, the settings should minimize the stall duration.

-- Script to correct autogrowth settings to SQL Server 2022 recommendations
-- Preview the output before executing

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER DATABASE [' + d.name + N'] MODIFY FILE (NAME = N''' + f.name + N''', FILEGROWTH = '
    + CASE f.type_desc
        WHEN N'LOG' THEN N'65536KB'    -- 64 MB
        ELSE              N'262144KB'   -- 256 MB
      END
    + N');'  + CHAR(13)
FROM sys.master_files   f
JOIN sys.databases      d ON f.database_id = d.database_id
WHERE d.database_id > 4
  AND d.state_desc = N'ONLINE'
  AND (
        f.is_percent_growth = 1
        OR (f.type_desc = N'LOG' AND f.is_percent_growth = 0 AND f.growth * 8 / 1024 <> 64)
      );

PRINT @sql;
-- Review the output then uncomment the line below to execute:
-- EXEC sp_executesql @sql;

10 The Complete IFI and TDE Behavior Matrix Beginner

All behavior in this matrix is verified against Microsoft Learn documentation as of July 2026.

11 Three Common Misconceptions Beginner

Misconception 1: TDE blocks all IFI everywhere

Accurate for data files in all SQL Server versions. Accurate for log files in all versions before SQL Server 2022. No longer the complete picture. TDE does not block IFI for transaction log autogrowth events up to 64 MB on SQL Server 2022 and later. A DBA who learned the rule before SQL Server 2022 has accurate knowledge about data files and needs an update only for log file behavior on SQL Server 2022.

Misconception 2: IFI showing as enabled means it is working for all files

IFI being enabled at the instance level (SE_MANAGE_VOLUME_NAME granted, “Instant File Initialization: enabled” in the error log) means data files on non-TDE databases benefit from IFI. Data files on TDE-enabled databases are still zeroed regardless of the IFI privilege status. The two controls are independent. TDE overrides IFI for data files regardless of whether the privilege is granted.

Misconception 3: The SQL Server 2022 log IFI change requires configuration

Nothing needs to be enabled. SQL Server 2022 applies IFI automatically to transaction log autogrowths up to 64 MB. SE_MANAGE_VOLUME_NAME is not required for this behavior. A SQL Server 2022 instance with TDE-enabled databases is already benefiting from faster log autogrowth for events of 64 MB or smaller, whether or not the DBA knows about this change. The only action worth taking is confirming that log autogrowth settings are set to 64 MB fixed increments so every event qualifies.

12 Workshop: Verify the SQL Server 2022 Behavior Intermediate

This workshop creates a TDE-enabled test database, starts the Extended Events session, triggers controlled log autogrowth, and confirms that log growth events complete in milliseconds even with TDE active. Run on SQL Server 2022 Developer Edition or a non-production SQL Server 2022 instance.

-- Step 1: Set up TDE infrastructure
USE master;

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'WorkshopKey!2026';

IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'WorkshopTDECert')
    CREATE CERTIFICATE WorkshopTDECert WITH SUBJECT = 'Workshop TDE Certificate';

-- Step 2: Create a small test database so growth events trigger quickly
IF DB_ID('IFI_TDE_Test') IS NOT NULL DROP DATABASE IFI_TDE_Test;

CREATE DATABASE IFI_TDE_Test
ON PRIMARY (NAME = N'IFI_TDE_Test', FILENAME = N'C:\SQLData\IFI_TDE_Test.mdf', SIZE = 64MB, FILEGROWTH = 64MB)
LOG ON    (NAME = N'IFI_TDE_Test_log', FILENAME = N'C:\SQLLogs\IFI_TDE_Test_log.ldf', SIZE = 32MB, FILEGROWTH = 64MB);
-- Log starts at 32 MB so the first growth event triggers quickly
-- Growth is set to exactly 64 MB, within the IFI ceiling

-- Step 3: Enable TDE
USE IFI_TDE_Test;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE WorkshopTDECert;
ALTER DATABASE IFI_TDE_Test SET ENCRYPTION ON;

-- Wait for encryption scan to complete
WAITFOR DELAY '00:00:10';

SELECT name, encryption_state_desc FROM sys.dm_database_encryption_keys dek
JOIN sys.databases d ON dek.database_id = d.database_id WHERE d.name = 'IFI_TDE_Test';
-- Should return: ENCRYPTED

-- Step 4: Start the Extended Events session (from Section 8)
-- If already created from Section 8, just start it
ALTER EVENT SESSION [Capture_FileGrowth] ON SERVER STATE = START;

-- Step 5: Fill the log to trigger autogrowth
USE IFI_TDE_Test;
CREATE TABLE dbo.GrowthTest (ID INT IDENTITY PRIMARY KEY, Payload NVARCHAR(4000) NOT NULL);

DECLARE @i INT = 0;
BEGIN TRANSACTION;
WHILE @i < 10000
BEGIN
    INSERT INTO dbo.GrowthTest (Payload) VALUES (REPLICATE(N'X', 4000));
    SET @i += 1;
    IF @i % 1000 = 0 BEGIN COMMIT TRANSACTION; BEGIN TRANSACTION; END
END
COMMIT TRANSACTION;

-- Step 6: Read the Extended Events results
-- Log growth of 64 MB with TDE should complete in single-digit milliseconds
SELECT
    event_data.value('(event/@timestamp)[1]', 'DATETIME2')       AS EventTime,
    event_data.value('(event/action[@name="database_name"]/value)[1]','NVARCHAR(128)') AS Database,
    event_data.value('(event/data[@name="file_type"]/text)[1]',  'NVARCHAR(50)')  AS FileType,
    event_data.value('(event/data[@name="size_change_kb"]/value)[1]','BIGINT') / 1024 AS SizeChangeMB,
    event_data.value('(event/data[@name="duration"]/value)[1]',  'BIGINT') / 1000     AS DurationMs
FROM (SELECT CAST(target_data AS XML) AS rb FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
    WHERE s.name = 'Capture_FileGrowth' AND t.target_name = 'ring_buffer') AS r
CROSS APPLY rb.nodes('//RingBufferTarget/event') AS x(event_data)
WHERE event_data.value('(event/action[@name="database_name"]/value)[1]','NVARCHAR(128)') = 'IFI_TDE_Test'
ORDER BY EventTime DESC;
-- Expected: LOG file, 64 MB growth, DurationMs = 1 to 10 ms
-- This confirms SQL Server 2022 log IFI is working on a TDE-enabled database

-- Step 7: Clean up
USE master;
ALTER EVENT SESSION [Capture_FileGrowth] ON SERVER STATE = STOP;
DROP EVENT SESSION [Capture_FileGrowth] ON SERVER;
DROP DATABASE IFI_TDE_Test;

References

• Microsoft Docs: Database Instant File Initialization (primary source for all IFI behavior described in this article)
• Microsoft Docs: Transparent Data Encryption (TDE)
• Microsoft Data Exposed: Instant File Initialization for the Transaction Log: SQL Server 2022 Hidden Gems (Bob Ward, Anna Hoffman)
• Microsoft Docs: TempDB Database
• SQLYARD: SQL Server TempDB Configuration, Monitoring, and Troubleshooting
• SQLYARD: SQL Server Performance Tuning: The Complete Guide