What is database initialization and how to set instant file initialization.

Problem: What is database initialization and how to set instant file initialization.

Solution: Database Instant File Initialization

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.

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.

Example:

In this example, I would like to show how we can set the database 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.