How to tell if you have instant initialization enabled?

As you may already know, instant file initialization is a way to prevent data (not log) file create and grow operations having to zero-initialize the new space before allowing it to be used. This can vastly speed up these operations as zero-initialization can take a *long* time for large files. This is especially useful in disaster recovery operations as the first phase of a restore is always to create the requisite files, if they don’t already exist. Cutting minutes or even hours from this phase can significantly reduce downtime and it’s in all editions of SQL Server from SQL Server 2005 onward. You can get more details from a blog post of Kimberly’s from March 2007 and note that it only applies to data files (see here for why not for log files).

The way to enable it is to give the SQL Server service account (or group that the service account is part of) the ‘Perform volume maintenance tasks’ privilege and then restart the service, or during installation/using the config tool for 2016 onwards. There’s no way to enable it from within SQL Server, and you can disable it using trace flag 1806.

If you’re running SQL Server 2014 SP2 or later, the state of instant file initialization is reported in the error log during instance startup, as below:

2017-06-16 11:16:49.15 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic ‘Database Instant File Initialization’ in SQL Server Books Online. This is an informational message only. No user action is required.

Before SQL Server 2012, enable trace flags 3004 and 3605 and create a dummy database. You’ll get a message in the error log indicating that SQL Server is zeroing out the log file for the new database. If you do *NOT* have instant file initialization enabled, you’ll see a similar message for zeroing out the data file of the new database. Don’t forget to turn the trace flags off again.

After SQL Server 2012, look in the output for the DMV sys.dm_server_services in the instant_file_initialization_enabled column to see whether it’s enabled or not.


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.