The question such as this makes my day during the Comprehensive Database Performance Health Check. One of the DBA asked if there is an easy way to know if they have Instant File Initialization Enabled or Not for their server. Honestly, this is a very simple question but there was no quick answer to this one before SQL Server 2016. However, if you are using SQL Server 2016 or later version of SQL Server, you can figure out the status of the instant file initialization pretty quickly.
In SQL Server, data files can be initialized instantaneously if this particular setting is enabled. 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. Remember, Log files cannot be initialized instantaneously.
Every time when SQL Server 2016 restarts, it writes into the error log the status of this configuration. We can open the SQL Server error log and scan through it to know the status of the Instant File Initialization or we can just run following command which will demonstrate the status in the result.
exec xp_readerrorlog 0, 1, N'Database Instant File Initialization'
When you run the above command it will display the following result.
You can clearly see in the result that there is a message which explains the status of the Instant File Initialization. Please note that the above method will work with any version of SQL Server.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
I think you mixed up your latest command. The xp_readerrorlog always works, But starting from SQL2016 SP1 you can also query sys.dm_server_services to check if IFI is enabled
You are absolutely correct and I modified the original blog post based on your comment.
Is there any method for versions prior to 2016? When was IFI introduced? –Kevin3NF