Instant File Initialization - SQL Server

In today’s blog post we will learn how we can enable Instant File Initialization if it is disabled for your server. The method which I am going to explain you works for pretty much all the versions of SQL Server.

Whenever SQL Server needs to allocate space for certain operations like creating/restoring a database or growing data/log files, SQL Server first fills the space it needs with zeros. In many cases, writing zeros across the disk space before using that space is unnecessary.

Instant file initialization (IFI) allows SQL Server to skip the zero-writing step and begin using the allocated space immediately for data files. It doesn’t impact growths of your transaction log files, those still need all the zeroes.

Should we fill this with zeroes?

WHY INSTANT FILE INITIALIZATION IS A GOOD IDEA

The larger the growth operation, the more noticeable the performance improvement is with IFI enabled. For instance, a data file growing by 20 GB can take minutes to initialize without IFI. Read more about waits for file growths here. This can make a huge difference whenever you are proactively growing out data files.

Bonus: Enabling IFI can also make restoring databases considerably faster, too!

WHY INSTANT FILE INITIALIZATION MAY NOT BE A GOOD IDEA

By not writing zeros across newly allocated space, it leaves the possibility open that deleted files may still exist in that space and be somehow accessible. The deleted files could be accessed through the backup file or if the database is detached. However, this risk can be mitigated by making sure the detached data files and backup files have restrictive permissions.

Also, IFI will not happen if Transparent Data Encryption (TDE) is in use.

How to Fix It

 SQL Server doesn’t have a setting or checkbox to enable IFI.

Just so you know that SQL Server can’t enable IFI, it must be enabled in Windows Security Policy. Here are the steps for the same.

• First, go to Start Menu and run secpol.msc to bring up Local Security Policy
• Expand the Local Policies Folder
• Click on User Rights Assignment
• Go to the Perform Volume Maintenance Tasks”” option and double click it
• Add your SQL Server Service account, and click OK
• Restart your SQL Server services (MUST DO)

If you add incorrect service account or forget to restart your SQL Server services, you will not see the impact of IFI on your server. 

If you are using SQL Server 2016 or later version of SQL Server, you can figure out the status of the instant file initialization pretty quickly.

In SQL Server, data files can be initialized instantaneously if this particular setting is enabled. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Remember, Log files cannot be initialized instantaneously.

Every time when SQL Server 2016 restarts, it writes into the error log the status of this configuration. We can open the SQL Server error log and scan through it to know the status of the Instant File Initialization or we can just run following command which will demonstrate the status in the result.

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

When you run the above command it will display the following result.

You can clearly see in the result that there is a message which explains the status of the Instant File Initialization. Please note that the above method will work with any version of SQL Server.