Export (0) Print
Expand All

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

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.

Note Note

Instant file initialization is available only on Microsoft Windows XP Professional or Windows Server 2003 or later versions.

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. For more information about assigning user rights, see the Windows documentation.

Instant file initialization is not available when TDE is enabled.

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.

Security Considerations

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. While the database file is attached to the instance of SQL Server, this information disclosure threat 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. A similar threat exists when the database is backed up. The deleted content can become available to an unauthorized user or service if the backup file is not protected with an appropriate DACL.

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

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

  • Disable instant file initialization for the instance of SQL Server by revoking SE_MANAGE_VOLUME_NAME from the SQL Server service account.

Note Note

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

Community Additions

ADD
Show:
© 2014 Microsoft