When you create (or grow) the size of a database file, SQL Server will initialize the allocated space on the disk, i.e. fill it with zeroes. If you’re adding a large amount of space to your database file, this operation can take quite some time to complete. But just like there’s a “quick format” in most operating systems, you can allocate large chunks of database file space without initializing it. This is called “Instant file initialization” in the world of SQL Server.
To enable Instant File Initialization, you need to provide the service account used by SQL Server with the correct permissions by adding it to a local or group policy in Windows. To do this, start the Local Security Policy manager, either from the start menu or by running “secpol.msc” from a command prompt:
Browse to Local Policies, User Rights Assignment, and add the service account (or a group that includes this account) to the policy “Perform volume maintenance tasks“. You will need to restart the SQL Server service before this change takes effect.
Instant File Initialization will not work in following cases:
- On log files
- On sparse files (snapshots)
- When using transparent data encryption (TDE)
- In clusters, you’ll have to grant the above right on each node.
The security aspect
Allowing a group or user to “perform volume maintenance tasks” enables them to read raw data from the disk, including files and sectors that they otherwise might not have been allowed to read. This is a potential security issue if you keep other stuff on the disk, and you haven’t properly secured access to the SQL Server and/or it’s service account. In most cases, this won’t affect you at all, nevertheless you should understand what you’re doing.
Credits
I first read about this feature in this excellent article on the Brent Ozar blog about SQL Server. The article actually contains a load of other good information, and is well worth a read.