Edit

Database Instant File Initialization

  • Article

Applies to: SQL Server

In this article, you learn about instant file initialization, and how to enable it to speed up growth for your SQL Server database files.

By default, 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 zeroing the files (filling with zeros) when you perform the following operations:

  • Create a database.
  • Add data or log files, to an existing database.
  • Increase the size of an existing file (including autogrow operations).
  • Restore a database or filegroup.

In SQL Server, for data files only, instant file initialization (IFI) allows for faster execution of the previously mentioned file operations, since it reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files.

Transaction log files cannot be initialized instantaneously, however, starting with SQL Server 2022 (16.x), instant file initialization can benefit transaction log autogrowth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization.

Enable instant file initialization

Instant file initialization is only available if the SQL Server service startup 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

Some feature usage, such as Transparent Data Encryption (TDE), can prevent Instant File Initialization.

Note

Starting with SQL Server 2016 (13.x), this permission can be granted to the service account at install time, during setup.

If using the command prompt install, add the /SQLSVCINSTANTFILEINIT argument, or check the box Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service in the installation wizard.

To grant an account the Perform volume maintenance tasks permission:

  1. On the computer where the data file will be created, open the Local Security Policy application (secpol.msc).

  2. In the left pane, expand Local Policies, and then click User Rights Assignment.

  3. In the right pane, double-click Perform volume maintenance tasks.

  4. Click Add User or Group and add the account that runs the SQL Server service.

  5. Click Apply, and then close all Local Security Policy dialog boxes.

  6. Restart the SQL Server service.

  7. Check the SQL Server error log at startup.

    Applies to: SQL Server (Starting with SQL Server 2012 (11.x) SP4, SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x) and later).

    1. If the SQL Server service startup account is granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged:

      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

    2. If the SQL Server service startup account has not been granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged:

      Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

    Note

    You can also use the column instant_file_initialization_enabled in the sys.dm_server_services DMV to identify if instant file initialization is enabled.

Security considerations

We recommend enabling instant file initialization as the benefits can outweigh the security risk.

When using instant file initialization, the deleted disk content is overwritten only as new data is written to the files. For this reason, the deleted content might be accessed by an unauthorized principal, until some other data writes on that specific area of the data file.

While the database file is attached to the instance of SQL Server, this information disclosure risk is reduced by the discretionary access control list (DACL) on the file. This DACL allows file access only to the SQL Server service account and the local administrator. However, when the file is detached, it may be accessed by a user or service that does not have SE_MANAGE_VOLUME_NAME.

Similar considerations exist when:

  • The database is backed up. If the backup file is not protected with an appropriate DACL, the deleted content can become available to an unauthorized user or service.

  • A file is grown using IFI. A SQL Server administrator could potentially access the raw page contents and see the previously deleted content.

  • The database files are hosted on a storage area network. It is also possible that the storage area network always presents new pages as pre-initialized, and having the operating system re-initialize the pages might be unnecessary overhead.

If the potential for disclosing deleted content is a concern, you should take one or both of the following actions:

  • Always make sure that any detached data files and backup files have restrictive DACLs.

  • Disable instant file initialization for the instance of SQL Server. To do so, revoke SE_MANAGE_VOLUME_NAME from the SQL Server service startup account.

    Note

    Disabling will increase allocation times for data files, and only affects files that are created or increased in size after the user right is revoked.

SE_MANAGE_VOLUME_NAME user right

The SE_MANAGE_VOLUME_NAME user privilege can be assigned in Windows Administrative Tools, Local Security Policy applet. Under Local Policies select User Right Assignment and modify the Perform volume maintenance tasks property.

Performance considerations

The Database File initialization process writes zeros to the new regions of the file under initialization. The duration of this process depends on size of file portion that is initialized and on the response time and capacity of the storage system. If the initialization takes a long time, you may see the following messages recorded in the SQL Server Errorlog and the Application Log.

Msg 5144
Autogrow of file '%.*ls' in database '%.*ls' was cancelled by user or timed out after %d milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Msg 5145
Autogrow of file '%.*ls' in database '%.*ls' took %d milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

A long autogrow of a database and/or transaction log file may cause query performance problems. This is because an operation that requires the autogrowth of a file will hold on to resources such as locks or latches during the duration of the file grow operation. You may see long waits on latches for allocation pages. The operation that requires the long autogrow will show a wait type of PREEMPTIVE_OS_WRITEFILEGATHER.

See Also

CREATE DATABASE (SQL Server Transact-SQL)