Effect of Instant File Initialization within SQL Server

By:   |   Comments (6)   |   Related: More > Database Administration


It is a best practice to pre-allocate databases and not to rely on autogrow to expand your database. Often times during a server migration, or setting up a new SQL instance, or during disaster recovery where downtime needs to be minimized you need to create a large database or you may want to just pre-allocate an exiting database file. I used to run into this scenario at my last company where it would take 30-45 minutes to create a large database.


When enabled, Instant File Initialization can create almost any sized data file instantaneously since it bypasses the zeroing out of the underlying bits on the file system. This privilege is set in the Local Security Policy and Administrators get this right by default, so if your service account happens to be an Admin (shame on you) then it already has this privilege set.

What Operations Are Affected Inside SQL Server

  • Database Creation
  • Add data file to existing database
  • Growing a data file
  • Restores
  • Backup Operations

Test Case 1 - Creating a Database

As a test I created a 50GB database before and after Instant File Initialization was enabled. My testing was performed using SQL Server 2012 CU1 on Windows Server 2008 R2. Also since this was a testing environment I was utilizing local disk.

Before Instant File Initialization was enabled it took 2 min to create this database (Note: This was local disk and would have taken longer on a SAN or external array).

Create Before INI

After setting Instant File Initialization this 50GB database was created in sub second time.

Create After INI

Test Case 2 - Growing a Database File

As a second test case I created a very small database and then grew the data file to 50GB simulating pre-allocation of an exiting database which aligns with best practices for database sizing if the size is not known at database creation time.

Before having Instant File Initialization set up, growing a data file to 50GB on my test system took around 2 min as shown below.

Alter Before INI

After setting Instant File Initialization this data file grew to 50GB in sub second time.

Alter After INI

Do I have Instant File Initialization Enabled?

The one way to tell without opening the Local Security Policy is to perform 2 quick tests:

  • Create a test database with a 10GB data file and a 1MB log file and note the duration
  • Create a test database with a 1MB data file and a 10GB log file and note the duration

Since Instant File Initialization applies to data files only, if these times are close to the same, odds are you do not have Instant File Initialization configured.

How To Enable Instant File Initialization

1. Open Local Security Policy and go to Local Policies --> User Rights Assignment.

Local Security Policy

2. Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.

Perform Volume Maintenance Tasks

3. Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.
Next Steps
  • Check your environment to see if you have Instant File Initialization enabled
  • Set your service account to use the "Perform Volume Maintenance Tasks" policy
  • Test the duration for creating databases and growing log files

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, December 12, 2013 - 10:11:16 AM - Pavel Pawlowski Back To Top (27780)

@daDbDeveloper, in the article it is mentioned correctly.

Related to the 10 GB DB. Sure, if the IFI is off, it will take longer to initialize the database file, however in the sample is 10 GB Database and 1  MB Log file. The second scenario I 1 MB Database and 10 GB Log File. Since Log files is always zeroed and IFI is not applied on the log, then the times for both scenarios should be close in case IFI is disabled.

Monday, September 23, 2013 - 11:18:23 AM - daDbDeveloper Back To Top (26897)

I would take exception to your comment, regarding testing if Instant File Initiation is set: Since Instant File Initialization applies to data files only, if these times are close to the same, odds are you do not have Instant File Initialization configured.

The opposite should be true, shouldn't it? if IFI is off, it should take longer to initialize the DB with 10GB data file.

Monday, August 20, 2012 - 6:44:01 PM - Chad Churchwell Back To Top (19135)

sqlfriend, after re-reading your comment I see what you are asking, since it is named "local" would it still apply to SAN and the answer is yes it would after the drives have been presented through disk management

Monday, August 20, 2012 - 4:15:55 PM - Chad Churchwell Back To Top (19134)

Yes to my knowledge this has the same effect on SAN drives a slocal drives.  There is not a setting called local security policy.  local security policy is a group of policies and the policy this is referring to is called "Perform Volume Maintenance Tasks"

Monday, August 20, 2012 - 12:41:59 PM - sqlfriend Back To Top (19129)

This setting is called local security policy. All our database files are on SAN drives.

So does this local setting have the same effect on SAN drives?



Monday, August 20, 2012 - 4:31:27 AM - Tony Back To Top (19119)

It's implied in your article but it's probably worth mentioning explicitly that, if for some reason your database is not fixed in size, as per best practice, autogrowth times of the data files (as per your example of manually adding a data file) are also improved by this setting.


get free sql tips
agree to terms