Instant File Initialization

Question

How can i check if IFI is enabled on my PROD server?

I did the following checks:

1. Ensured that the service account under which SQL Server is running is part of Administrators group.

2. Perform Volume Maintenance tasks permission is set for "Administrators"

Does this mean that IFI enabled?

Answer 1

Easiest way is to

• enable TF 3004 and 3605 (turn them off when done)
• Create a dummy database
• Check in error log for "SQL Server is zeroing out ..."

SQL Server 2016 will log the message by default when Instant file Initialization is enabled ... instant file initialization is enabled

Obviously, you can use xp_cmdshell whoami /priv or tsql way.

Good PFE blog reading : How and Why to Enable Instant File Initialization

Answer 2

SQL Server 2016 now has a checkbox during install to set the IFI option for the service account.

However, after install the best way is to see what user the DB Engine is running under. Then see if that account has the right permissions.

1. Go to Administrative Tools
2. Go to Local Security Policy
3. Expand Local Policies
4. Expand User Rights Assignment
5. Open "Perform volume maintenance tasks"
6. Confirm the SQL Engine account is listed here.

Remember that running SQL Server service accounts in Administrators group is NOT best practice. See Microsoft's link on how to set correct permissions here: https://msdn.microsoft.com/en-us/library/ms143504.aspx Per that link, use SQL Server Configuration Manager to change user accounts, as it will set the minimum required permissions for you.

Answer 3

As of SQL Server 2016 SP1 this information is available in the instant_file_initialization_enabled column of the sys.dm_server_services DMV.

Applies to: Starting SQL Server 2016 SP1.

Specifies whether instant file initialization is enabled for SQL Server Database Engine service. This property does not apply to services (example: SQL Server Agent) other than SQL Server Database Engine service. nullable.

Y = instant file initialization is enabled for the service.

N = instant file initialization is disabled for the service.

Null = doesn’t apply to service.