On-Demand Instant File Initialization status with sys.dm_server_services

Configuring a SQL Server with Instant file initialization is a fairly standard part of SQL Server Installation Checklists. There are substantial benefits to the duration taken during a SQL Server database restore.   If you want to read more on how to set up instant file initialization and the technical benefits read SQL Server faster restores with instant file initialisation

When I first started using instant file initialization I used to manually check using the gpedit.msc console - and checking the  Windows Settings | Security settings | Local Policies | User Rights Assignments. Eventually I wrote a script to check as part of the installation process . But it still required a separate process to check .

As we're continuously  looking for automation opportunities especially for pre checks and other validations  , it is now much easier with the instant file initialization value made available through the sys.dm_server_services  view

The Microsoft documentation describes sys.dm_server_services   as "Returns information about the SQL Server, Full-Text, SQL Server Launchpad service (SQL Server 2017+), and SQL Server Agent services in the current instance of SQL Server. Use this dynamic management view to report status information about these services"

The "instant_file_initialization_enabled" column , specifying whether instant file initialization is enabled for the SQL Server Database Engine service. The benefit of the instant file initialization being available through the view is it can be extracted via a simple sql statement

select instant_file_initialization_enabled from sys.dm_server_services where servicename = 'my_service_name'

Read more on sql server installation

SQL Server Install Checklist

SQL Server database files configuration checklist - SQL Server