By: Mike Eastland | Comments (7) | Related: More > DBA Best Practices
Problem
I have heard that enabling instant file initialization is a SQL Server best practice. How do I enable it and what is the impact of doing so?
Solution
The status of instant file initialization is determined by permissions granted to the account used to start the SQL Server database engine service. It is not enabled by default unless a Local Administrator account is used. However, this is not recommended. See this tip for additional information on SQL Server service accounts.
Before enabling SQL Server instant file initialization, let's first examine the impact of not using this feature. As with any advice found on the internet, be sure to test these steps in a non-production environment to ensure that the full impact is understood before deploying to production.
Enable SQL Server Trace Flags for Instant File Initialization
These undocumented trace flags, 3004 and 3605, will output additional information related to creating or altering files to the SQL Server instance log.
-- Listing 1 USE [master] GO DBCC TRACEON (3004, 3605, -1) GO
See these links for more information on SQL Server trace flags and the DBCC TRACEON command.
Create a Test Database
Listing 2 creates a database while instant file initialization is disabled. The file paths and sizes may need to be modified for other environments.
-- Listing 2
CREATE DATABASE [MSSQLTips_Zero]
ON PRIMARY (NAME = N'MSSQLTipsZero_Data',
FILENAME = N'e:\mssql\data\MSSQLTipsZero_Data.mdf',
SIZE = 8192MB,
FILEGROWTH = 0)
LOG ON (NAME = N'MSSQLTipsZero_Log',
FILENAME = N'c:\mssql\log\MSSQLTipsZero_Log.ldf',
SIZE = 256MB,
FILEGROWTH = 0)
GO
Review the SQL Server Error Log
After creating the database, the SQL Server Error log should show something similar to the following:
Both the data file and transaction log file for the new database are zero-initialized. This indicates that the operating system is filling the files with zeroes when it is created. Also note the time stamp of the log entries. On this test system, creating the database took approximately 2 minutes and 30 seconds.
For more information on the SQL Server Error log, check out these tips.
Enable Instant File Initialization in SQL Server
The first step is to identify which account is used to start the SQL Server database engine service. The Services console can be used to check this. The service display name may be different based on the version of SQL Server and whether a default or named instance is used.
Once the service account has been identified, additional rights must be granted to the account. To do this, open the Local Security Policy console from Control Panel\Administrative Tools.
Next, navigate to User Rights Assignment under Local Policies.
Then, select the Perform Volume Maintenance Tasks policy and add the service account (sql_svc in this example) to the list. As mentioned previously, the Local Administrators group should already have this permission by default.
Finally, restart the SQL Server service via your method of choice. This is required for the permission changes to take effect on the service account.
Re-Enable Trace Flags
Since the database engine service was restarted, the code from Listing 1 must be executed again to re-enable the SQL Server Trace Flags described above.
Create Another Test Database
Listing 3 creates a database with instant file initialization enabled. The location and size of the files are identical to the database created in Listing 2.
Again, be sure to update the file paths and sizes as needed.
-- Listing 3
CREATE DATABASE [MSSQLTips_Init]
ON PRIMARY (NAME = N'MSSQLTipsInit_Data',
FILENAME = N'e:\mssql\data\MSSQLTipsInit_Data.mdf',
SIZE = 8192MB,
FILEGROWTH = 0)
LOG ON (NAME = N'MSSQLTipsInit_Log',
FILENAME = N'c:\mssql\log\MSSQLTipsInit_Log.ldf',
SIZE = 256MB,
FILEGROWTH = 0)
GO
Review the SQL Server Error Log Again
Only the transaction log file is zero-initialized when instant file initialization is enabled. For additional information on why instant file initialization does not apply to transaction log files, see this blog post. Also, the time to create the database on the same test system decreased from 2 minutes 30 seconds to 2 seconds.
Conclusion
This tip shows one way to enable instant file initialization for SQL Server along with the I/O and time savings that can be realized by doing so. The example was created using SQL Server 2014 SP1 on Windows 8.1, but should be relevant to any currently-supported SQL Server configuration. Be sure to fully test prior to implementing in production.
Next Steps
- Enable instant file initialization on your SQL Servers using the steps outlined above.
- Automate this activity within your SQL Server build and deployment process.
- Review the following links for additional information:
- MSDN: Database instant file initialization
- Paul Randal: Misconceptions around instant file initialization
- Check out more tips related to SQL Server Best Practices.
About the author
Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
