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.
Step 3- In the left pane, expand Local Policies, and then click User Rights Assignment.
Step 4- In the right pane, double-click Perform volume maintenance tasks.
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.
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 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
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.
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018