January 16, 2014

Subscribe

Perform Volume Maintenance task in database creation

by Scott Newman

If you have to do any file growing\restoring\creation at all, best grant the Perform volume maintenance tasks in the group policy edior (run –> gpedit.msc –> Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment) to the account that Sql Server is running under. 

Just a short totally subjective note on the performance of creating a 5GB database with a 1GB log file.  Without the Perform volume maintenance tasks right, this took about 2 minutes to complete. 

After adding the service account to the Perform volume maintenance tasks (and restarting the Sql Server Service via configuration manager [a must]), the database takes 17 seconds to create. 

When you add the service account to the Perform volume maintenance tasks it enables sql server to skip zeroing out the data file after it allocates space for it.  Unfortunately, this can’t be done for the log file though.  It must be zero’d out when it is grown or created.