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
