How to Check Status of Instant File Initialization? – Interview Question of the Week #184

Question: How do I check whether Instant File Initialization is enabled?

Answer: Check the Database Engine row in sys.dm_server_services, or find the IFI startup message in the SQL Server error log.

The DMV gives the service’s status directly:

SELECT servicename, status_desc,
       instant_file_initialization_enabled
FROM sys.dm_server_services;

Y means enabled for the Database Engine service; N means disabled. The column doesn’t describe SQL Server Agent’s file initialization. It’s available from SQL Server 2016 SP1, and was also added to SQL Server 2012 SP4 and SQL Server 2014 SP2. Earlier builds can’t run that column selection.

For this DMV, SQL Server 2019 and earlier require VIEW SERVER STATE. SQL Server 2022 and later require VIEW SERVER SECURITY STATE.

Check the Error Log Too

For the current error log, search for the initialization message:

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

If the current log has cycled since startup, check the archived startup log too. Not finding the message in this one file doesn’t by itself establish that IFI is disabled. Error-log access has its own permission requirements.

What IFI Covers

IFI normally avoids zero-filling newly allocated data-file space when the Database Engine has the necessary Windows privilege. TDE can prevent data-file IFI. Transaction logs are mostly zero-filled, with one exception: from SQL Server 2022, log autogrowth of up to 64 MB can benefit from IFI without that privilege. Larger log-growth events don’t.

The service setting is therefore not proof that every file operation will use IFI. Which service, file type, SQL Server version and operation you are investigating still matters. Is IFI enabled on your production Database Engine? If it’s intentionally disabled, I would be interested in the reason.

IFI is not a database setting, it is a service privilege, so check the Database Engine row and the file type you care about.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.