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(http://www.sqlskills.com/blogs/kimberly/post/Instant-Initialization-What-Why-and-How.aspx ), Paul Randal (http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx) 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 (http://blogs.msdn.com/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx) 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

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...