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

Question: How to Check Status of Instant File Initialization?

Answer: This is a follow-up question of the previous blog post which I shared earlier. Please read this blog post before reading this blog for complete information: How to Check If IFI Enabled or Not?

The easiest and simplest way is to find out the status of the instant file initialization (IFI) is to check the error logs. If you run the following script you can quickly identify the status if IFI.

exec xp_readerrorlog 0, 1, N'Database Instant File Initialization'

Another method is to use the DMV sys.dm_server_services. Here is the script which can help you identify the status of the IFI.

SELECT ServiceName, status_desc, 
instant_file_initialization_enabled
FROM sys.dm_server_services

Here is the resultset of the query:

Please note that the above method will only work with SQL Server 2016 and above as the column of IFI was introduced in SQL Server 2016 SP1.

Remember that IFI basically applies to SQL Server Engine services and also only to the data file. For the log file, IFI is not applicable at all.

I would be interested to know if INI is enabled on your production server or not. If it is not enabled, it would be interesting to know the reason for the same. Please leave a note in the comment section with the reason.

Well, that’s it. In the future blog posts, we will discuss how we can enable Instant File Initialization for your server.

Reference: Pinal Dave (https://blog.SQLAuthority.com)