SQL Server Database Instant File Initialization

After building new SQL server one of the most important post configuration required to setup for increasing database performances  is Instant File Initialization (IFI)( you can also change it on existing server). When SQL server require to perform some specific operations like;

  • Create a new database.
  • Add new file to an existing database.
  • Increase the size of an existing files including Auto growth
  • Restore a database

For example, when you perform any of the above tasks,  the default setting is for sql server to reserve space using zeros ‘0’, but under IFI, the space is allocated without  the initial zeros which in turn increases performance on above operations.

How to Enable Instant file Initialization (IFI)

Identify the SQL server service account


Make sure service account is enrolled in server administrator groups


In Windows search box ,type local security policy


From perform volume maintains task property, add SQL server service account to this group


Must  restart the SQL server service in order to implement this change to SQL server


-By : MD Ullah




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s