Basics of SQL Server Database Instant File Initialization


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 operations like 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.

In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

How to Enable Database Instant File Initialization in 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. Important point to note regarding instant file initialization is that Instant file initialization is not available when TDE is enabled.

To grant an account the Perform volume maintenance tasks permission:
On the computer where the backup file will be created, open the Local Security Policy application (secpol.msc).
In the left pane, expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Perform volume maintenance tasks.
Click Add User or Group and add any user accounts that are used for backups.
Click Apply, and then close all Local Security Policy dialog boxes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Leave a Reply

Your email address will not be published. Required fields are marked *