SQL Server 2019 Configuration Manager provides an interface for instant file initialization. My lab also used it with SQL Server 2017.
- In SQL Server 2019 Configuration Manager, select SQL Server Services.
- Open the intended Database Engine service Properties and select Advanced.
- Inspect Instant File Initialization. Approve any change against the service identity, policy and maintenance window.
- Apply an approved change, complete the planned restart and verify the effective value using the query below.
The named instance used NT SERVICE\MSSQL$SQL2017. Configuration Manager granted Perform volume maintenance tasks to the relevant service identity. I did not assign that privilege manually in this demonstration.
SELECT servicename, instant_file_initialization_enabled FROM sys.dm_server_services;Inspect the effective engine setting before and after a planned change. Confirm the service identity and governing policy. Complete the required service restart. A dropdown change does not immediately change every running instance.
IFI avoids zero-initializing eligible data-file growth. SQL Server 2022 also permits eligible log growth up to 64 MB to benefit. Encryption and release affect data-file rules. I still plan sensible file sizes and growth settings.
Reference: Instant file initialization requirements and limits.
Related reading
- SQL SERVER – How to Turn On / Enable Instant File Initialization?
- How to Check Status of Instant File Initialization? – Interview Question of the Week #184
- SQL SERVER – How to Check If Instant File Initialization Enabled or Not?
An IFI interface change is not proof of an effective engine change, it is a configuration step to verify.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
2 Comments. Leave new
Thank you for this helpful article, Mr. Dave. Although, I have a question. I’m working as MSSQL DBA in a mid-range company.
We have many SQL servers, and we use domain accounts to run the MSSQL Engine Service. We recently had an workshop with a MSSQL expert, who recommended that we should put the domain account for MSSQL Engine under Local Policy called ” Perform Volume Maintenance Tasks”.
We always check for “Grant Perform Volume Maintenance Tasks for MSSQL Engine” during the installation, hence the NT SERVICE \MSSQLSERVER is already granted this right, when I check the Local Policies.
So my question is, is it enough to have granted the NT SERVICE\MSSQSERVER “Perform Volume Maintenance Tasks”, or should also the domain account (that runs the MSSQL Server Engine Service” also have the right?
Hi Dave! when i set Instant File Initialization in SQL Server 2019 Configuration Manager and press OK i have error: Not All privileges or groups referenced are assigned to the caller [0x80070514]
how to fix it?