Instant File Initialization

Here's a little tidbit I wanted to share regarding the Perform Volume Maintenance Tasks security setting. In the SQL Server world, this is often referred to as IFI. On more recent versions of SQL (SQL 2012 SP4 or later, I believe), you can verify if IFI is enabled or not for the database engine logon account by checking the error log. Here's an example:

It's effective, but doesn't lend itself well to automation or scripting. That's where the DMV sys.dm_server_services comes in. I use it frequently to check the logon account for the database engine and SQL Agent services. But I've lazily used a SELECT * query and I missed out on the instant_file_initialization_enabled column, which was all the way to the right of the results grid in SSMS and off screen. Here's a not-as-lazy query that demonstrate what I had been missing out on:

SELECT s.servicename, s.service_account,
 s.instant_file_initialization_enabled
FROM sys.dm_server_services s

Curiously, the data type for the column is NVARCHAR(1), with Y/N values. Most of the other "boolean" values in other DMVs are BIT fields of 0 or 1. Also note the IFI setting only applies to the row that corresponds to the database engine service. All the other rows have N, whereas N/A would be more appropriate IMO.

Nitpicks aside, this is great! Any time I can retrieve a setting with a query, I'm a happy camper. 😁