By: Chad Churchwell | Comments (6) | Related: More > Database Administration
Problem
It is a best practice to pre-allocate databases and not to rely on autogrow to expand your database. Often times during a server migration, or setting up a new SQL instance, or during disaster recovery where downtime needs to be minimized you need to create a large database or you may want to just pre-allocate an exiting database file. I used to run into this scenario at my last company where it would take 30-45 minutes to create a large database.
Solution
When enabled, Instant File Initialization can create almost any sized data file instantaneously since it bypasses the zeroing out of the underlying bits on the file system. This privilege is set in the Local Security Policy and Administrators get this right by default, so if your service account happens to be an Admin (shame on you) then it already has this privilege set.
What Operations Are Affected Inside SQL Server
- Database Creation
- Add data file to existing database
- Growing a data file
- Restores
- Backup Operations
Test Case 1 - Creating a Database
As a test I created a 50GB database before and after Instant File Initialization was enabled. My testing was performed using SQL Server 2012 CU1 on Windows Server 2008 R2. Also since this was a testing environment I was utilizing local disk.
Before Instant File Initialization was enabled it took 2 min to create this database (Note: This was local disk and would have taken longer on a SAN or external array).
After setting Instant File Initialization this 50GB database was created in sub second time.
Test Case 2 - Growing a Database File
As a second test case I created a very small database and then grew the data file to 50GB simulating pre-allocation of an exiting database which aligns with best practices for database sizing if the size is not known at database creation time.
Before having Instant File Initialization set up, growing a data file to 50GB on my test system took around 2 min as shown below.
After setting Instant File Initialization this data file grew to 50GB in sub second time.
Do I have Instant File Initialization Enabled?
The one way to tell without opening the Local Security Policy is to perform 2 quick tests:
- Create a test database with a 10GB data file and a 1MB log file and note the duration
- Create a test database with a 1MB data file and a 10GB log file and note the duration
Since Instant File Initialization applies to data files only, if these times are close to the same, odds are you do not have Instant File Initialization configured.
How To Enable Instant File Initialization
1. Open Local Security Policy and go to Local Policies --> User Rights Assignment.
2. Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.
3. Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.
Next Steps
- Check your environment to see if you have Instant File Initialization enabled
- Set your service account to use the "Perform Volume Maintenance Tasks" policy
- Test the duration for creating databases and growing log files
About the author
Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
