SQL Server Instant File Initialization (IFI)

You can check if Instant File Initialization (IFI) is enabled in SQL Server using a T-SQL query, by reviewing the SQL Server error log, or via the Windows Local Security Policy. 

Using T-SQL (Recommended for SQL Server 2012 SP4+ and 2016 SP1+) 

The sys.dm_server_services dynamic management view (DMV) provides a direct programmatic way to check the status of IFI. 

Run the following query in SQL Server Management Studio (SSMS):

sql

SELECT 
    servicename, 
    instant_file_initialization_enabled
FROM 
    sys.dm_server_services
WHERE 
    servicename LIKE 'SQL Server (%' OR servicename = 'MSSQLSERVER';

A result of Y in the instant_file_initialization_enabled column indicates IFI is enabled. A result of N means it is disabled. 

Checking the SQL Server Error Log

When the SQL Server instance starts, an informational message is logged in the error log indicating the status of IFI. 

Run the following T-SQL command to read the error log for the relevant message: 

sql

EXEC xp_readerrorlog 0, 1, N'Database Instant File Initialization';

The output will clearly state whether the feature is enabled or disabled. 

• Enabled message: “Database Instant File Initialization: enabled.”
• Disabled message: “Database Instant File Initialization: disabled.” 

Verifying Windows Permissions

IFI is enabled at the Windows operating system level by granting the SQL Server service account the “Perform volume maintenance tasks” user right (also known as SeManageVolumePrivilege). 

1. Identify the SQL Server Service Account:
   Open the SQL Server Configuration Manager to find the “Account Name” used by your SQL Server instance service.
2. Check Local Security Policy:
   • On the Windows server hosting the SQL Server instance, open the Local Security Policy editor by running secpol.msc from the Run dialog (Windows Key + R).
   • Navigate to Security Settings > Local Policies > User Rights Assignment.
   • Double-click on the policy “Perform volume maintenance tasks”.
   • Check if the SQL Server service account (or a group it belongs to) is listed. 

If the account is present, IFI is enabled. Changes to this policy require a restart of the SQL Server service to take effect. For further details, refer to the Microsoft Learn documentation on Instant File Initialization.