Sql-server – Instant File Initialization

sql server

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?

Best Answer

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

Related Question