Select Page

What is Instant About Instant File Initialization for SQL Server?

Author: Pinal Dave | | August 3, 2016

This feature surfaced for the first time in SQL Server 2005. Though in my opinion, this is a critical feature, not many people know about the impact it has from a performance point of view. In this blog, I will show you how to enable this feature and provide a quick demo that exhibits how it drastically improves performance.

What does instant file initialization achieve? Faster creation of databases, faster data file growth (whenever it happens), and faster restores. It is critical, however, to know that this works only on the Data files and doesn’t apply to Log files. The necessary privilege is Perform Volume Maintenance Tasks when we are talking about Instant File Initialization.

I always enable this on my laptop whenever it gets refreshed; here’s how to do it: Make sure to open up SQL Server Configuration manager -> Select SQL Server Services -> SQL Server and select the Properties. Under the “Log On”, make sure to take a note of the “Account Name”. In your environment, this can be different or in a domain joined machine where the service logon was changed, these will be different values.

Instant File Initialization - Manager

Before I start taking you through the process, I’ll create a database (50GB Data File and 10GB Log file). This is to check the amount of time it takes to create this database on my laptop. It took almost 2:59 mins to perform this operation. This is shown below:

Instant File - Database

For the sake of completeness, let me give you the script that I used. These timings will vary in your environment based on HDD speed.



USE [master]







( NAME =N'Instant-File-DB',FILENAME=N'C:\Data\Instant-File-DB.mdf',



( NAME =N'Instant-File-DB_log',FILENAME=N'C:\Data\Instant-File-DB_log.ldf',

      SIZE = 10240MB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )


The next logical step is to add the permission mentioned earlier. For this run, the secpol.msc on your machine. Now under “Local Policies” -> “User Rights Assignment” -> Perform volume maintenance task -> “Add user or Group…” and make sure to add the “Log On” MSSQLServer Service account as we noted in step 1. In my example, I have added “NT Service\MSSQLSERVER” user account as shown in the figure below.

Instant File - Policy

After adding this permission, make sure to restart the SQL Server Service once. After this, just rerun the database creation script as shown before. The same script now runs faster at just under 23 seconds.

Instant File - Restart

The improvement we got by enabling the Instant File Initialization feature is substantial; from 3 minutes to 23 seconds. Though this capability seems to deliver ultimate and mind blowing performance, in a few of the production environments I have seen this is being disabled because of security concerns. Since the file creation doesn’t quite zero the sectors, there might be a way to do DBCC PAGE and get data that was stored inside the filesystem. Though this is beyond the scope of this blog, we can surely take a look at those edge cases in a future post. Were you aware of this feature? Have you enabled it in your environments? Let me know in the comments.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.


You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.