Perform Volume Maintenance task in database creation

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.

image

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.

image

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.

image

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: