Taking Advantage of Instant File Initialization

Following up a bit on the last post regarding autogrowth in SQL Server, a related setting that’s worth taking note of is Instant File Initialization (IFI).

Instant File Initialization is especially helpful when large file growths are occurring. Without Instant File Initialization, SQL Server has to write zeros to the disk in order to initialize it before SQL Server can use it for new data. As with autogrowth, the amount of time this process takes depends on the size. The more disk space that is needed, the longer it’s going to take for the disk to be zeroed for use. Fortunately, IFI exists to skip the need to write out zeroes. Instead, disk space can immediately be used as needed.

IFI can also make a noticeable difference when restoring databases. If you’re working on getting your recovery time objective (RTO) down, check the status of IFI. Enabling IFI may help cut that number down to an acceptable value.

There is a small security risk that comes by enabling IFI. Since zeros are not being written out to new disk space, previously deleted data could possibly still be accessed until it is overwritten. With this in mind, it’s worth considering that Microsoft still recommends using IFI “as the benefits can outweigh the security risk.”

Let’s walk through checking if IFI is enabled and then enabling it. The first way to check is to search the against the error log with the following script:

exec xp_readerrorlog 0, 1, N'Database Instant File Initialization'

This same message is in the log file if you prefer going into the log file viewer and searching for it there:

This query can also give you information about IFI being enabled:

SELECT servicename, service_account, instant_file_initialization_enabled
FROM sys.dm_server_services;
GO

Now that we’ve confirmed IFI is disabled based on the “N” value, let’s get it enabled. To do so, we’ll have to leave the comfort of SSMS and change permissions of the service account running SQL Server.

First, we want to run secpol.msc:

Drill down to Local Policies > User Rights Assignment > Perform volume maintenance tasks:

Double click the policy to edit its settings. Click Add User or Group… to add the SQL Server service account and click OK.

We’ll need to restart SQL Server for the change to take effect but after we do, we should see that IFI is now enabled:

IFI will not have an impact on log files but can be a significant time saver when growing other data files and restoring databases.

Thanks for reading!