Skip to content

database-instant-file-initialization.md

Latest commit

 

History

History
109 lines (80 loc) · 7.94 KB

database-instant-file-initialization.md

File metadata and controls

109 lines (80 loc) · 7.94 KB
title Database Instant File Initialization | Microsoft Docs
ms.custom
ms.date 01/09/2018
ms.prod sql-non-specified
ms.prod_service database-engine
ms.service
ms.component databases
ms.reviewer
ms.suite sql
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
initializing files [SQL Server]
instant file initialization [SQL Server]
fast file initialization [SQL Server]
file initialization [SQL Server]
IFI [SQL Server]
database instant file initialization [SQL Server]
ms.assetid 1ad468f5-4f75-480b-aac6-0b01b048bd67
caps.latest.revision 33
author BYHAM
ms.author rickbyh
manager jhubbard
ms.workload Active

Database File Initialization

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md] 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 one of 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.

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.

Instant File Initialization (IFI)

In [!INCLUDEssNoVersion], data files can be initialized instantaneously to avoid zeroing operations. Instant file initialization 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.

Note

Instant file initialization is available only on [!INCLUDEmsCoName][!INCLUDEwinxppro] or [!INCLUDEwinxpsvr] or later versions.

Important

Instant file initialization is available only for data files. Log files will always be zeroed when being created, or growing in size.

Instant file initialization is only available if the [!INCLUDEssNoVersion] 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.

To grant an account the Perform volume maintenance tasks permission:

  1. On the computer where the backup 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 any user accounts that are used for backups.

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

Note

Starting with [!INCLUDEssSQL15], 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.

Note

Starting with [!INCLUDEssSQL11] SP4, and [!INCLUDEssSQL15] SP1 through [!INCLUDEssCurrent], the column instant_file_initialization_enabled in the sys.dm_server_services DMV can be used to identify if instant file initialization is enabled.

Remarks

If the [!INCLUDEssNoVersion] service startup account is granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged in the [!INCLUDEssNoVersion] error log at startup:

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.

If the [!INCLUDEssNoVersion] service startup account has not been granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged in the [!INCLUDEssNoVersion] error log at startup:

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.

Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL11] SP4, [!INCLUDEssSQL14] SP2 and [!INCLUDEssSQL15] through [!INCLUDEssCurrent])

Security Considerations

When using Instant File Initialization (IFI), because the deleted disk content is overwritten only as new data is written to the files, 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 [!INCLUDEssNoVersion], this information disclosure risk is reduced by the discretionary access control list (DACL) on the file. This DACL allows file access only to the [!INCLUDEssNoVersion] 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. A similar consideration exists 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.

Note

If [!INCLUDEssNoVersion] is installed in a secure physical environment, the performance benefits of enabling instant file initialization can outweigh the security risk and hence the reason for this recommendation.

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 [!INCLUDEssNoVersion] by revoking SE_MANAGE_VOLUME_NAME from the [!INCLUDEssNoVersion] service startup account.

Important

Disabling instant file initialization will increase allocation times for data files.

Note

Disabling instant file initialization only affects files that are created or increased in size after the user right is revoked.

See Also

CREATE DATABASE (SQL Server Transact-SQL)