Pages

Friday 19 October 2012

Instant initialisation of sql server files



There is a feature in windows 2003 and above that enables sql server to instantly initialise files to the required size without delay. It is called instant file initialization.

This is very useful when growing data files, adding new files and Filegroup and if we ever have to restore any database.
In order for Sql server to use this feature, the Sql server service account needs to be granted “perform volume maintenance tasks” permission.

For this to take effect, Sql server needs to be restarted.

Kimberly Tripp has a very good blog about this which you can read here 

She explains how to enable this in the link above 

"Granting the permission "Perform Volume Maintenance Tasks"
To use instant initialization, your SQL Server service must be running with an account that has the required privilege. If your SQL Server service is running as a local administrator this permission already exists. For a service account which is not a local administrator (again, recommended!), the necessary privilege to grant is Perform Volume Maintenance Tasks. This permission can be granted by an administrator through the Local Security Policy tool (Start, All Programs, Administrative Tools) and once granted, SQL Server automatically uses instant initialization. IMPORTANT NOTE: If this permission is given while SQL Server is running, then SQL Server must be stopped and restarted. However, once the server is restarted, no other syntax or permissions are needed. All data file creation/extension options will use instant initialization for data files created on NTFS volumes when SQL Server 2005 is running on Windows XP or Windows 2003 Server."