What is Instant File Initialization and How to enable it on SQL Server Instance?

Have you ever thought how SQL Server initialized its database files during space allocation? General way to initialize any data and log files is to filling the files with zeros to overwrite any existing data left on the disk from previously deleted files. Data and log file initialization happen during space allocation activities such as database creation, adding any database files, Autogrow operation or increasing the size of any database files and backup file creating.

What is Instant File Initialization?

Data and log file initialization takes lot of time if you have to create large database files. This can also increase total completion time of large database restore or large amount of data import to the SQL Server. SQL Server has a feature called Instant file initialization that allows fast data file allocations of the all file operations. 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. But unfortunately, this feature doesn’t work with log files so they cannot be initialized instantaneously. This is not enterprise feature any SQL Server edition can use it.

As we know DELETE operation deallocates the space and a new allocation can reuse that space which may still have data in it. When we do not zero out the existing space in case of instant file initialization there is a possibility that someone could read data that you thought had been deleted. This is a known security risk of this feature that must be considered while enabling it for your SQL Server Instance. Instant File Initialization is not enabled by default in SQL Server because of this security risk.

How to Enable Instant File Initialization?

Below are the steps you need to perform to enable Instant File Initialization.

Step 1- Login to server where you want to enable Instant File Initialization. This might be your database server or backup server.
Step 2- Open the Local Security Policy application. You can go to Control Panel->Administrative Tools->Local Security Policy. You can also open it directly by putting secpol.msc into run command prompt.

Open Local Security Policy by running secpol.msc

Step 3- In the left pane, expand Local Policies, and then click User Rights Assignment.

local security policy
Step 4- In the right pane, double-click Perform volume maintenance tasks.

Enable Instant file Initialization
Step 5- Click Add User or Group and add any user accounts that are used to run SQL Server services. You can get SQL Server service account from SQL Server Configuration Manager or from services.msc console.

SQL Server Services

Now add account Domain\techyaz to above policy. It would be great if you create a group and add this account to that group and then add that group to given policy so that every time you don’t need to add user account here if SQL Server service account got changed.


Step 6- Once SQL Server service account will be added, it will look like below image. Click Apply, and then close all Local Security Policy dialog boxes.


Step 7- Restart SQL Server services to apply this change.

If you need to enable this feature on Cluster environment, make sure to perform this activity on all cluster nodes. If you are running with multiple SQL Server Instances, add all required security logins/group to this policy.

Now if you think how to know Instant File Initialization is enabled on your SQL Server Instance then here is the answer. You should check SQL Server logs to get the details about Instant File Initialization. When SQL Server service starts, database engine checks all details of SQL Server configurations and capture them into log file. You can see my error log file after restarting SQL Server service. Instant File Initialization is showing as enabled on this server.

2017-06-16 11:16:49.15 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic ‘Database Instant File Initialization’ in SQL Server Books Online. This is an informational message only. No user action is required.

instant file initialization in sql server log file

Advantages

Instant File Initialization improves performance of multiple database activities where large size of data file creation or Autogrow operation will perform. This can vastly speed up these operations as zero-initialization can take a long time for large files. Below are the operations that can take advantage from this feature.

  • Data Load/Import
  • New Database Creation
  • Database Restore
  • Autogrow Operation
  • Data file size Increase
  • Backup file Creation

Limitations

This feature will not work if:

  • Your file is a log file.
  • The account has not the required privilege.
  • It is a sparse file – Database Snapshots.
  • Transparent Data Encryption (TDE) is enabled.
  • Trace flag 1806 is enabled to disable instant file initialization.

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Manvendra Deo Singh
Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *