Whenever a new database is created or a database is being restored SQL Server physically zero’s out every 8k page in the database and that is when it takes some to actually initiate the database creation or restore process. Based on the size of the database it is often a time consuming process. When we have the Instant file initialization setup SQL Server doesnt have to do this as the space is considered to be allocated by the OS execlusively to sql server abruptly. It is possible only if the sql server service account has the SE_MANAGE_VOLUME_NAME privileges granted by the OS to zero out the space and this privilege is set by default to all the administrators. Unless SQLServer service account is part of the administrator’s group the service account has to be added to “Perform Volume Maintenance Tasks” security policy to get these privileges. Below is the screen shot to add the service account to security policy mentioned above.
Go to start–> run and type secpol.msc
Now press OK and restart the sql server service.
Only MDF and NDF files are affected by the Instant File Initialization and ldf files cant take the advantage of it. So if you are trying to restore a database and log file is large then although the mdf file would have been created instantly it will take some time for the ldf file to get created and it applies whenever the log file size is increased manually or if there is an autogrowth. IFI is used
At the time creating a new database
When we increase the size of a database manually or if its due to an autogrowth
When we restore database backups
Whenever tempdb gets recreated (thats whenever SQL Server is restarted)
Hope this helps