I’ve been conducting quite a few SQL Server health assessments lately so I thought I would write a brief explanation of the importance of “Instant File Initialization” and how I check to see if it’s being used.

SQL Server File Initialization

The SQL Server storage engine utilizes an initialization process on data and log files. This process fills the files with zeros in order to overwrite any existing data that may be on disk. The initialization process occurs when you create a database, restore a database or file group, add files to an existing database or increase the size of an existing database file. These disk operations are blocking processes that will cause SQL Server to wait until they are complete.

What is Instant File Initialization?

Simply put, instant file Initialization removes the file initialization penalty by negating the need to fill the newly allocated space with zeros before it can be used. This is incredibly important and can reduce disk allocation times from minutes to milliseconds. This is incredibly important when you think about restoring a VLDB and how long you’ll have to wait just for the empty database files to be initialized.

There are a couple of caveats to mention. The first is that instant file initialization is only available on Windows Server 2003 or later. If you’re on Windows Server 2000 then you’re out of luck. The second is that this only works for database data files and is not available for transaction log allocations. The last thing to note is that this is not available if you’re using Transparent Data Encryption (TDE) in SQL Server.

Am I Using Instant File Initialization?

If you don’t know if you’re using this capability then I recommend you find out as soon as possible. Instant file initialization is only working for you if the SQL Server service account has been granted SE_MANAGE_VOLUME_NAME .

Figuring this out from the comfort of SSMS turns out to be pretty simple but it does require the use of xp_cmdshell in order for the query I’ve provided to work. If your security policies restrict the use of xp_cmdshell then you’ll have to figure this out the hard way or turn on xp_cmdshell before you execute and then turn it off after you’re done. If xp_cmdshell is enabled then execute the following script to see if you’re receiving the benefit:

EXECxp_cmdshell'whoami /priv';
GO

What have we just done? Well, we’ve just executed the Windows “whoami” command in the context of our database service account. We included the /priv parameter because otherwise this tool returns quite a bit of information and we only need to see the security privileges. It’s important to note that the output will return all the security privileges whether they are granted to the account or not. The key is to look to the right of the privilege to see whether is disabled or enabled. Have a look at the output below:

If you scan down to the bottom of the list you’ll find the SE_MANAGE_VOLUME_PRIVILEGE near the bottom. You’ll see that to the right the privilege it is listed as enabled. This is great news and we’re not paying the initialization penalty during data file allocations.

Wait, Instant File Initialization is not Enabled!

If you executed the query and came to the unfortunate conclusion that instant file initialization is not enabled, don’t worry because it’s very easy to implement.

First off, open up the “Local Policy Editor” on the database server and navigate to the “User Rights Assignment” node under the “Local Policies” node. When you select the “User Rights Assignment” node all of the policies will be displayed in the pane at the right.

You’ll need to scroll down to find policy. It’s named “Perform volume maintenance tasks.” You’ll notice that the users or groups that have been granted this privilege are listed to the right. Open the policy and add the SQL Server service account.

Unfortunately, once this is complete you’re still not finished. You’ll need to restart the SQL Server service for it to take effect.

Conclusion

I think it’s easy to see how this feature will benefit your environment in a number of situations. So, take the time today to check this out and save yourself all the waiting when time matters most.

Scott Caldwell, SQL Server DBA