Quick Scan Report – Instant File Initialization (IFI)

What this check looks for

The SQL Server error log, for the entry SQL Server writes at startup stating whether the service account holds the Perform volume maintenance tasks privilege. The message reads:

Database Instant File Initialization: enabled.

or

Database Instant File Initialization: disabled. For security and performance considerations see the topic ‘Database Instant File Initialization’ in SQL Server Books Online.

Because it reads the error log, the check depends on the log being readable, which has its own finding.

Why it matters

Without it, every byte of new data file space is written with zeros before SQL Server will use it.

That happens on:

• Every autogrow. A 1 GB growth means writing 1 GB of zeros, during which writes to that file wait.
• CREATE DATABASE and any ALTER DATABASE ... MODIFY FILE that increases size.
• Every restore, because a restore creates the files first.

The time is proportional to the size and the storage speed. On spinning disks a 100 GB restore can spend tens of minutes zeroing before a single page of data is written. On SSD it is faster and still entirely wasted: SQL Server overwrites the space as it uses it, so the zeros serve no purpose for the database.

The restore case is the one that matters most. Restore time is what you are measured on during an incident, and this setting can be a large fraction of it. It is also entirely free to change, which makes it one of the better value items in this whole report.

Log files are not covered. Transaction log files are always zeroed, on every version, with no exception and no setting. That is by design: log recovery depends on being able to tell where the valid log ends, and zeroed space is how it does. So enabling this speeds up data file growth and restores of data files, and does nothing for log growth.

The security consideration is real and small. Without zeroing, the space allocated to the file still contains whatever the operating system last had there. Someone with the ability to read raw pages from the file could see fragments of previously deleted data from elsewhere on the disk. The exposure requires direct file access, which is already a serious compromise. The check’s own description notes this: enable it unless you are following regulatory restrictions that forbid it.

How to confirm it yourself

From the error log, which is authoritative:

EXEC sp_readerrorlog 0, 1, N'Instant File Initialization';

Read the previous logs too if the instance restarted recently:

EXEC sp_enumerrorlogs;
EXEC sp_readerrorlog 1, 1, N'Instant File Initialization';

On SQL Server 2016 SP1 and later there is a DMV column:

SELECT [servicename],
       [service_account],
       [instant_file_initialization_enabled]
  FROM sys.dm_server_services WITH (NOLOCK)
 WHERE [servicename] LIKE N'SQL Server (%';

Which account needs the privilege:

SELECT [servicename], [service_account]
  FROM sys.dm_server_services WITH (NOLOCK);

And what it is costing you, from the default trace’s autogrow events:

SELECT [DatabaseName], [FileName], [StartTime],
       [Duration] / 1000 AS [duration_ms], [EventClass]
  FROM sys.fn_trace_gettable(
         CONVERT(NVARCHAR(500),
           (SELECT [value] FROM sys.fn_trace_getinfo(1) WHERE [property] = 2)), DEFAULT)
 WHERE [EventClass] = 92    -- data file autogrow
 ORDER BY [Duration] DESC;

A data file autogrow taking seconds rather than milliseconds is this setting.

How to fix it

Grant the Windows privilege to the SQL Server service account. There is no T-SQL for it; it is a Windows local security policy setting.

1. Open Local Security Policy (secpol.msc) on the server.
2. Go to Local Policies, then User Rights Assignment.
3. Open Perform volume maintenance tasks.
4. Add the SQL Server service account, exactly as sys.dm_server_services reports it. For a default instance running as a virtual account that is NT SERVICE\MSSQLSERVER; for a named instance, NT SERVICE\MSSQL$INSTANCENAME.
5. Restart the SQL Server service. The privilege is read at startup.
6. Confirm from the error log that it now says enabled.

On SQL Server 2016 and later the installer offers this as a checkbox during setup, which is the easiest moment to get it right.

It is per server, not per instance, in the sense that the privilege is a Windows setting, but each instance’s service account needs it individually.

Group Policy will undo it if your domain manages this right centrally, which is a common cause of it reverting after working for months. If the server is in an OU with a policy defining that user right, the change has to be made there.

While you are at it, this setting only helps if file growth is sensibly sized. A database growing in 1 MB steps still grows constantly; instant file initialization just makes each tiny growth instant rather than merely slow. Both growth checks on this report are worth reading alongside this one.

How long it takes

About an hour, nearly all of it arranging a window to restart the service.

Related reports

Related checks

Frequently asked questions

Does it help log file growth? No. Log files are always zeroed, on every version, by design. This affects data files only.

Is the security concern serious? It is real and narrow: unzeroed space can contain fragments of previously deleted data from elsewhere on the disk, readable by someone with direct file access. If an attacker has that, you have larger problems. Regulated environments sometimes forbid it regardless.

Do I need a restart? Yes. The privilege is checked when the service starts.

It was enabled and now it is not. Group Policy is the usual explanation. If a domain policy defines the “Perform volume maintenance tasks” right, it overwrites the local setting on refresh.