0 Comments
  •   Posted in: 
  • SQL

By default, SQL Server 0’s out database files as it allocates space for them. This can be good for security/reliability in a production environment, but it means SQL Server is writing a bunch of extra data. Turn on instant file initialization in dev/stage/test environments for dramatically faster database creation and backup/restore operations.

There’s no switch for this in SQL Server, strangely enough. To get instant file initialization enabled, you’ve got to grant the “Perform volume maintenance tasks” permission to the SQL Server service account.

Brad McGehee’s got a great detailed analysis of what happens with instant file initialization and walkthrough steps with screenshots.