The time needed for creating large and very large database can often take many minutes. SQL Server 2005 has a new feature called Instant File Initialization (IFI) that can speed up the data file initialization of a database.
Database File initialization
The pages in the Data and Log files are initially filled with zeros when one of the following database tasks is performed:
- CREATE DATABASE
- Creating the TEMPDB database at service startup
- Autogrow of databases activity (including TEMPDB)
- RESTORE DATABASE from backup media
- ALTER DATABASE…MODIFY FILE
The filling zero process can affect the performance of SQL Server. SQL Server 2005 has a new feature called Instant File Initialization (IFI). This feature skips zeroing out of the data pages. Instant File Initialization reduces the time during the creation of large and very large databases.
When installing SQL Server 2005, the login account of the service, by default, runs under the Local System account. Microsoft recommends that DBA’s use a named Windows user (in the case of workgroup environment) or using a domain user account whe the server is a member of domain.
By default these accounts do not have the right “Perform Volume Maintenance Tasks”. In other words, the SQL Server service login account isn’t granted the security policy “SE_MANAGE_VOLUME_NAME”. As a result SQL Server can’t perform instant initialization and perform the filling zero process during database file creation which can take a long time.
Enabling Instant Data File Initialization
Instant initialization is only used for data files (not log files). This feature is available only on Microsoft Windows XP, Windows Server 2003 or later versions. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.
Configuring a SQL 2005 service Account to Enable Instant File Initialization
- Log into the SQL Server 2005 machine using an account with administrative privileges.
- Determin the SQL Server 2005 service account name . You can get the account name from the services applet or from SQL Server Configuration Manager:
- Click Start, Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager
- Click SQL Server 2005 Services. The services will be displayed in the right pane
- Double-click the SQL Server service, the SQL Server (MSSQLSERVER) properties window is displayed (the service may be with other name, if you use a named service)
- In the login tab, you will find the login account name
- Click Start, select Settings and click Control Panel
- In Control Panel, double-click Administrative Tools
- In Administrative Tools, double-click “Local Security Settings”
- In Local Security Settings window, expand Security Settings, and then expand Local Policies
- Select the User Rights Assignment folder. The policies will be displayed in the right pane
- In the right pane, select Perform Volume Maintenance Tasks
