Getting rid of Instant File Initialization (or enabling it if that strikes your fancy)

Published On: 2008-12-04By:

SQL Server 2005 introduced us to Instant File Initialization. This allows SQL Server to create files of any size without sitting there for minutes or hours (depending on the size of the files).

While this is great when creating your database, or extending your database files there is a cost to doing so. Before each data page is written the SQL Server will write all zeros to the page. It also has the potential of a security issue as any data fragments which are in the space which the file took up are going to be included in the backup and could then be read if the backup was lost.

For these reasons (I consider the performance issue bigger than the security issue, because if someone has access to the backup of your database you’ve got bigger problems then some data fragments in your backup) you can disable this feature.

It’s a stupid fix, but at least you can do it. Open the Local Security Policies MMC snapin from MMC, or the Administrative Tools window. Under Local Policies > User Rights Assignment find the “Perform volume maintenance tasks” option. Remove the Administrators group from this right, and put in any accounts which need the right to remotely defrag the hard drives (if any). The SQL Service will need to be restarted in order for the change to take effect.

If you run your SQL Server under the local system account, or another built in account you can not revoke this right and will need to run the SQL Server under a domain account, or local server account.

If your SQL Server runs under a domain account or a local account which is not a member of the local Administrators group then you do not have this right by default (you can give this right to an account which isn’t in the Administrators group by granting it the right listed above).

If you remove this right, make sure that your SQL Server’s auto grow settings are not set crazy high otherwise your transactions may fail while SQL Server waits to write zeros to the disk.

I usually recommend setting the auto grow to 512 Megs or 1 Gig unless the database grows more then that per day. A setting of 512 Megs to 1 Gig is small enough that the database can grow and zero it out quickly.

The best way to manage growth is obviously to manage is manually allocate very large amounts of disk at a time and only using auto grow as a backup.

I hope you find this useful.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link