If you’ve spent any time tuning SQL Server, you’ve probably heard “Turn on instant file initialization, it’ll speed things up!” That’s mostly true… but when it comes to transaction log files, the story is a bit more complicated.
In this post, we’ll peek under the hood of instant file initialization (IFI), see why it gives data files all the love and mostly ignores log files, look at what changed in SQL Server 2022, and talk about sane log sizing in the real world.
What instant file initialization actually does
When SQL Server creates or grows a database file, it normally has to zero out the new space on disk for security reasons. That means writing zeros to all the newly allocated bytes so someone can’t read leftover data that used to live on that part of the disk.
Instant file initialization skips that zeroing step for data files (mdf/ndf), as long as:
- The SQL Server service account has the “Perform volume maintenance tasks” privilege (SE_MANAGE_VOLUME_NAME).
- Transparent Data Encryption (TDE) is not enabled for that database’s data files.
SQL Server will overwrite whatever’s on disk as real data pages are written later.
With IFI enabled, operations like:
- Creating a new database,
- Growing data files, and
- Restoring databases
can be noticeably faster, especially with large file sizes. Instead of staring at a “frozen” SSMS window while SQL Server writes zeros, you get your file allocations almost instantly.
That’s great for the data files. What about the log files?
Why transaction log files are different
Transaction log files (ldf) are a different beast. SQL Server depends on the log being strictly sequential and fully known so it can:
- Guarantee crash recovery works correctly.
- Keep log sequence numbers (LSNs) consistent.
- Preserve the ACID properties you actually care about when the storage subsystem decides to have “a moment.”
To maintain that integrity, SQL Server has historically required that log files be fully zero-initialized whenever:
- A log file is created, or
- A log file grows.
There are no shortcuts or IFI magic. That means:
- IFI helps with data files.
- IFI does not help with log files under the classic behavior.
So if you create or grow a large log file, say, tens or hundreds of gigabytes, and your storage isn’t blazing, you can see long pauses while SQL Server zeros that new space. This is why DBAs get burned when a log autogrows by a massive chunk in the middle of the day.
SQL Server 2022: the 64 MB twist
Starting with SQL Server 2022, there’s a helpful optimization for transaction logs, under specific conditions.
The change:
- Log autogrowth events up to 64 MB can benefit from an IFI‑like optimization.
- Autogrowths larger than 64 MB still require full zero‑initialization.
- This only affects log autogrowth, not the initial creation of the log file.
SQL Server 2022 also sets the default log autogrowth for new databases to 64 MB to align with this optimization.
Here’s the 2026 reality check, though:
- 64 MB is tiny compared to the size of even average databases today.
- For a 50 GB database, 64 MB is a small bump.
- For a 1 TB database, 64 MB is basically a rounding error.
So yes, this feature is nice, and it can shave a bit of pain off small log growths, but it’s a convenience, not a design principle. You still need to size your log properly.
Security, TDE, and IFI
IFI interacts with security and encryption in a few key ways.
For data files:
- IFI requires the SQL Server service account (or service SID) to have the volume maintenance privilege.
- When you enable TDE, instant file initialization for data files is effectively off for that database; data files must be fully initialized.
For log files:
- Classic behavior: logs are always fully zero-initialized, regardless of IFI or TDE.
- In SQL Server 2022+, the ≤ 64 MB log autogrowth optimization does not require the volume maintenance privilege and still works with TDE enabled.
So:
- Data files: IFI + no TDE + privilege = faster file operations.
- Log files: still carefully controlled, with a small optimization in SQL Server 2022+ for tiny autogrowths.
Practical sizing: 50 GB database
Let’s start with a small-to-medium sized 50 GB database, something common but not tiny.
Assumptions:
- Around 50 GB of data.
- Steady OLTP workload, with some peaks during batch jobs.
- Regular log backups.
Data file sizing
You don’t want constant data file growth, even if IFI makes it fast.
A reasonable approach:
- Initial data size: 55–60 GB.
- Autogrowth: 512 MB or 1 GB, fixed (not percentage).
This keeps growth events relatively infrequent and predictable, and IFI makes them fast when they happen.
Log file sizing
Assume:
- Peak log usage during heavy windows: 20–30 GB.
- Log backups every 5–15 minutes.
A practical configuration:
- Initial log size: 25 GB
- Autogrowth: 512 MB or 1 GB fixed.
Why this range?
- 512 MB or 1 GB increments are big enough that you won’t grow constantly, but not so big that each growth is a terrifying event.
- You’re sizing the log so autogrowth is the exception, not the rule.
Should you use 64 MB autogrowth here?
You could set log autogrowth to 64 MB to ride the SQL Server 2022 optimization, but:
- If the log needs an extra 8 GB, that means 128 tiny growth events.
- That’s a lot of metadata work, and it fragments allocation.
- Overall, it’s usually worse for performance than just sizing correctly with sensible GB-based growth.
Use 64 MB autogrowth only if you really know your workload and have a specific reason. For most 50 GB databases, 512 MB or 1 GB increments are a more practical middle ground.
Practical sizing: 1 TB database
Now let’s move up to a relatively large 1 TB database, the kind where mistakes in log sizing are not “oops,” they’re “call everyone, we have an incident.”
Assumptions:
- About 1 TB of data.
- Busy OLTP or mixed workload.
- Significant batch work or ETL.
- Regular log backups.
Data file sizing (1 TB)
You want to avoid data growth during peak hours as much as possible.
A reasonable pattern:
- Initial data size: 1.1–1.2 TB (some headroom).
- Autogrowth: 8–16 GB fixed.
Example:
- Initial size: 1.1 TB.
- Autogrowth: 8 GB.
Why:
- Even 1% of a 1 TB file is 10 GB, so percentage-based growth is dangerous.
- 8 GB increments are large but manageable, and IFI helps these grow faster.
- You still try to grow mostly during maintenance windows, not at noon on Monday.
Log file sizing (1 TB)
Assume:
- Peak log usage during big operations: 200–300 GB.
- Frequent log backups (e.g., every 5 minutes during heavy activity).
Here, you really want to avoid autogrowth under load.
A conservative but realistic configuration:
- Initial log size: 300–400 GB.
- Autogrowth: 4–8 GB fixed.
For example:
- Initial size: 350 GB.
- Autogrowth: 4–8 GB.
Why this range instead of a higher 16–32 GB?
- 4–8 GB is still substantial at this scale but not absurdly large.
- If you ever do hit growth, the stall is noticeable but not catastrophic.
- You’re still designing so growth is rare; you’re not “using” autogrowth as part of normal operations.
Why 64 MB is basically noise here
For a 1 TB database, a 64 MB log growth is almost nothing:
- If you need an extra 64 GB of log space, that’s 1024 separate 64 MB growths.
- That’s an overhead factory and a fragmentation generator.
- Chasing the 64 MB optimization at this scale is the wrong problem to solve.
So for big systems:
- Choose multi-GB log growth increments that make sense for your workload.
- Pre-size the log generously (hundreds of GB if needed).
- Treat the 64 MB optimization as a nice perk if it ever kicks in, nothing more.
The bottom line
- Instant file initialization is fantastic, for data files.
- Transaction logs have stricter rules and still need to be fully initialized, with only a small 64 MB optimization in SQL Server 2022.
- In 2026, 64 MB is tiny compared to real-world database sizes, so this feature is nice but not game-changing.
- The real win is still boring and reliable: pre-size your log based on observed usage, use sensible fixed autogrowth increments (in MB or low single-digit GB), and avoid relying on autogrowth during peak load.
SQL Server 2025
There are no changes to these rules or behavior in SQL Server 2025. However, this does not rule out the possibility that a future cumulative update could alter this behavior.