SQL Server with Mr. Denny

Dec 4 2008   7:55AM GMT

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: