How to Enable Instant File Initialization SQL Server

There are few features in SQL Server that are disabled, or turned OFF, by default that, in my opinion, should be turned on/enabled. One of those features is something called “Instant File Initialization” (You can read about the other one here).

What is Instant File Initialization?

According to Books Online:

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:

  • Create a database.
  • Add files, log or data, to an existing database.
  • Increase the size of an existing file (including autogrow operations).
  • Restore a database or filegroup.

File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.

How to Enable Instant File Initialization SQL Server

Enabling IFI is quite simple:

  1. Open secpol.msc and expand the Local Policies Folder
  2. Click on User Rights Assignment
  3. Scroll down until you see the “Perform Volume Maintenance Tasks” and double click it
  4. Click the “Add User or Group” and add the SQL Server service account, click OK.
  5. Restart SQL Server

Done.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.