20 January,2011 by Jack Vamvas
Instant File Initialization can make SQL Server Restores much faster .
A typical SQL Server Restore phase includes the following phases:
a) Copy data,logs,index from backup to the database files
b) Take logged transactions and apply to data for recovery point rollforward.
Next step to undo phase – to deal with uncommitted transactions.
c) Undo takes uncommitted transactions and rolls back (which depending on the transaction can take awhile to rollback)– then making the database available for use
But , previous to these steps and in the process of restoring – the initialisation process of the data\log files fills the previously deleted files with zeroes .This is a OS security feature and is designed to avoid revealing information from previously deleted files .
In terms of SQL Server for certain situations (create database, restore database – when in conjunction with CREATE DATABASE ,increase size of a file, add files to a db) the OS will attempt to zero out .
The file initialisation process delays the restore process - because SQL Server initialises files by filling the space with zeroes. Just to clarify – this is in situations where a CREATE DATABASE is required as part of the RESTORE process
Is there a quicker way of restoring?
In certain situations ,yes, by applying Instant file initialisation .skips the the zeroing out of the data on disk – i,e it does not overwrite the existing data . In effect the OS allocates the space.
How to set up instant file initialization?
Prereqs: sql server 2005 , either Windows XP , widnows 2003 or later
1) Run the MSSQLSERVER service account with a Windows Acct
2)Assign SE_MANAGE_VOLUME_NAME special privilege to that Windows Account
Do this by :
On cmd line : gpedit.msc
Then : Windows Settings | Security settings | Local Policies | User Rights Assignments
Open “Perform volume maintenance tasks” and and add the SQL Server service acct
3) Recycle SQL Server instance
1) There are security consideration to analyse. The risks are minimal – but exist
2) The benefits derived from instant file initialization – only relate to the data files not the log files. You can test this out by restoring different databases with different size data and log files
3) In production environments I use this in some situations – not all restores.