Enable Instant Data File Initialization (Video)

Jon Seigel

Any time a new portion of a database file (data file or log file) is created, by default, SQL Server writes out zeros — to start with a clean slate, so to speak.

As the name implies, instant data file initialization is a feature that allows SQL Server to skip the zeroing process for data files. Log files are always zeroed out (because of the way they work internally).

The reason why you’d want to turn on this feature is simple: skipping the zeroing process does less I/O, thereby speeding up the process. This gives many advantages:

  • Recover faster: The tempdb database must be recreated on instance restart. This could be particularly important if you have an availability SLA to maintain.
  • Restore faster: Data files are initialized before the actual backed-up data pages are copied into the data files.
  • Better response time: If a data file has to auto-grow because of user activity, the user won’t have to wait as long for the operation to complete.

Now, of course there is a tradeoff here, and in this case it’s security-related: because no zeroing of the files happens with this setting turned on, it may be possible (through erroneous SQL Server behaviour) to access the previously-written data on the disk, which could be absolutely anything. While this is a very, very small risk, it may be too much for your environment. If that’s the case, this setting should be turned off (which is the default setting).

In this short video demo, I walk through the steps to enable the feature, and validate that it’s been successfully enabled.

Summary of steps:

  1. Add the database engine service account local group/domain group/service SID to the Perform Volume Maintenance Tasks security policy. (Note: this can also be accomplished using Windows Active Directory Group Policy, if that’s a better solution for your environment.)
  2. Restart the database engine service.
  3. Validate the feature is enabled using the script below. There shouldn’t be any messages in the Error Log that indicate data files were zeroed.
DBCC TRACEON(3004);
DBCC TRACEON(3605);

CREATE DATABASE abc;