There are few features in SQL Server that are disabled, or turned OFF, by default that, in my opinion, should be turned on/enabled. One of those features is something called “Instant File Initialization” (You can read about the other one here).
What is Instant File Initialization?
According to Books Online:
Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:
- Create a database.
- Add files, log or data, to an existing database.
- Increase the size of an existing file (including autogrow operations).
- Restore a database or filegroup.
File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.
How to Enable Instant File Initialization SQL Server
Enabling IFI is quite simple:
- Open secpol.msc and expand the Local Policies Folder
- Click on User Rights Assignment
- Scroll down until you see the “Perform Volume Maintenance Tasks” and double click it
- Click the “Add User or Group” and add the SQL Server service account, click OK.
- Restart SQL Server
As per Sql Server 2016, this is enabled by default during installation
Thanks Peter! If I recall, I don’t think it’s actually enabled by default. You have to “click” the option to enable it during SQL Server setup. It will go and assign all the privileges in the background.