Systems Engineering and RDBMS

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: