SQL server

Faster DB restores through instant file initialization.

Whenever SQL performs certain filesystem related activities like: Create/Restore a DB, add log or data files, increase current file size, etc… , the SQL engine initializes the files and fills them up with zeros. There is some added costs to this process, and it can be avoided to speed up the activities mentioned above. To enable instant file initialization, the SQL service account should be added to the ‘Perform volume maintenance task’ security policy.

Go to the local security settings under Start>Control Panel>Administrative tools>Local security policy or you can type secpol.msc at the command line/run prompt. Under  the local security settings panel go to Local Policies> User Rights Assignment> Perform volume maintenance tasks. Double click on the policy, and add the SQL service account to the Local security setting. Once the SQL service is restarted, instant file initialization will be used. Only files created after this policy change will be affected. To remove instant file initialization (possible security concern?), go to the Security policy panel described above, and remove the SQL service account from the ‘Perform volume maintenance task’ policy.

Advertisements
Standard

One thought on “Faster DB restores through instant file initialization.

  1. Pingback: Viewing VLFs in the SQL log file. « Stanley's Database Blog

Thinking about someting? 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