On-Demand Instant File Initialization status with sys.dm_server_services

03 July,2020 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on On-Demand Instant File Initialization status with sys.dm_server_services


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer