Instant File Initialization in SQL Server

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. This mean that every byte of new allocated space is overwritten with zero values (0×0). This behavior prevents the problem of accessing old data, that was previously stored physically in the same NTFS clusters. 

The zero-initialization takes place during the following SQL Server operations which causes these operations to take longer.:

          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 file group.

If you don’t want that SQL Server is doing the zero-initialization of data files then you can re-configure SQL Server. 

Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account 
has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

After enabling the Instant file initialization SQL Server will skip the zero-initialization of the data files, but you need to restart SQL server afterwards.

We can Apply this setting only to data files  – log files are always zero-initialized in SQL Server! There is no way through which you can change it!!! Without the zero-initialization of the log file, the crash recovery process would have no idea where to stop, when the log file was wrapped around. Crash Recovery stops where it finds zero values in the header of the next log record to be processed.

How to Check if Instant File Initialization is Enabled or not:

You can check that instant file initialization is enabled or not by creating a Test database. If it's not enabled then you can see messages in the SQL Server Error Log for the zeroing out both data file and log file. If it’s enabled then the messages will only be for Log File. Before that you need to turn on the trace flags for 3004 and 3605. After creation of database you can check the messages in error log file.

--=========================================================
USE master;
--Enable Trace Flags 3004 and 3605 to On.
DBCC TRACEON(3004,-1);
DBCC TRACEON(3605,-1);
--Create a sample database to see what output is written to the SQL Server Error Log
CREATE DATABASE IntantFileInitialization ON  PRIMARY
(NAME = N'IFI', FILENAME = N'D:\DBROOT\IntantFileInitialization.mdf', SIZE = 3000MB)
 LOG ON
( NAME = N'IFI_log', FILENAME = N'D:\DBROOT\IntantFileInitialization_log.ldf', SIZE = 1MB)
 go
--Off the Trace Flags.
DBCC TRACEOFF(3004,3605,-1);
--Remove the Database
DROP DATABASE IntantFileInitialization;
--==========================================================

--Now see the output in the SQL Server Error Log File

From above screen shot it is clear that Instant file Initialization is not enabled.  If it’s enabled then the messages will only be for Log File as below:

In order for SQL Server to  perform instant file initialization the SQL Server service account must be granted the Perform Volume Maintenance Task security permission. This can be done by using the Local Security Policy Editor via:

Administrative Tools – Local Security Policy and then Local Policies – User Rights Management as below:

Then add the rights to the SQL Server service account:

So you can enable Instant File Initialization for your SQL Server instance when you are the SQL Server & Windows administrator, it will be good to grant that permission, because as a Windows admin, you will always have access permission to the file system.

Thanks For Reading This Post!!