Recently, I made a post on why it takes a lot of time when you run CREATE DATABASE (or other data file extension tasks). The reason is database file initialization. This initialization happens for both LOG and DATA files, but you can skip this step for DATA files (not LOG files).

Recently, I learnt from Glenn Berry that we could leverage Instant Database File Initialization feature by skipping the step of zeroing out the data file, when:

• New database is created with a large data file size or
• Restoring a VLDB or
• Autogrowth of data file.
• etc.

This could be achieved by utilizing a feature, called Instant Database File Initialization, within the Operating System. This allows skipping the zeroing out step and complete the task much quicker. This is an operating system feature and needs special permissions. We need to assign this permission to the user account under which Sql Server service runs.

For that, go to:

Group Policy >> Computer Configuration >> Windows Settings >> Security Settings >> Local Policies >> User Rights Assignment >>

Look for a policy called “Performance Volume Maintenance“. By default, only administrator have this right, but you can assign this permission, on the local machine, to Sql Server service account (even if it is a domain account).

Restart the SQL instance and voila !! now the Sql Server service account has permissions to ‘Instant Database File Initialization’.

Hope this helps,
_Sqltimes