Database Instant File Initialization
Posted by decipherinfosys on August 30, 2008
Not many people know about Database File Initialization that can be done in SQL Server 2005. This MSDN post mentions it pretty clearly what the benefits are by initializing the data and log files. So, what is the advantage of this feature and how can we make use of this feature are the two questions that come to mind immediately.
The advantages are that when performing operations like database creation, file additions to an existing user database, restoring a database or a filegroup and increasing the size of the database, having database instant file initialization can reduce the time of these operations. This feature skips out the zeroing out of the data on the disk and thus does not overwrite the data written on the disk. The OS allocates the disk space and the contents of the file is written down to the disk. This is available on all editions of SQL Server 2005 on Windows Server 2003 and Windows XP on NTFS filesystem.
But in order to make use of this feature, you would need to configure your instance to take advantage of this feature. Here is what you need to do in order to configure the instance to take advantage of this feature:
1) Open up Local Security Settings after going to Start/All Programs/Administrative Tools/Local Security Policy.
2) The screen that will come up will be this:
3) Double click on “Perform volume maintenance tasks” entry and add the SQL Server Service account or the local group SQLServerMSSQLUser$instancename.
Also remember that this is just for the data files so in case you have a large txn log file, the data file will be initialized instantaneously but not the log file. The log file has to be zeroed out before being written to the disk.