Thursday, 21 January 2010

Instant File Initialisation

I have been reading up on instant file initialization and I have read a couple of good blog posts by Kimberly Tripp( ), Paul Randal ( and Cindy Gross. From SQL Server 2005 onwards if you grant the SQL Service Account “Perform volume Maintenance tasks” then with the necessary permissions SQL Server, if circumstance permit, can take advantage of not having to zero out a file allocation. (NOTE: This does not work on the log file) which can greatly enhance the speed of operations like data file growths (either initiated automatically or manually), database creation, and probably most importantly database restore operations.

Cindy Gross in her post ( recommend that you give permission to the appropriate SQL Server group as giving it to a specific user account. That way if the SQL Service account changes the new service account will be able to make use of instant file initialisation.

Cindy in her post talks you through enabling this feature for your SQL Server. I won’t repeat the steps and I would recommend you read the post if you need a step by step guide, but in essence you give the relevant SQL Server instance group permissions to Perform volume Maintenance tasks using secpol.msc snap-in.

No comments:

Post a Comment

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...