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

pic1

Make sure service account is enrolled in server administrator groups

pic2

In Windows search box ,type local security policy

pic3

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

pic4.png

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s