How and Why to Enable Instant File Initialization

You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. "Perform volume maintenance tasks") for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the "Perform volume maintenance tasks" local security policy. In a cluster you have to grant the right on all nodes. If there are multiple instances on a server or cluster, you should grant this right to each instance’s security group.


This permission keeps SQL Server from "zeroing out" new space when you create or expand a data file (it is not applied to log files).  This helps performance for CREATE DATABASE, ALTER DATABASE, RESTORE, and AUTOGROW. It can have a significant positive impact on how long it takes to create or expand a data file, but there is a small security risk in doing so. That is because a file "delete" really just deallocates the space and a new allocation can reuse that space which may still have data in it. When you do not zero out the existing space there is a possibility that someone could read data that you thought had been deleted. It is very common to turn Instant File Initialization on. Many shops consider the increased performance benefit to far outweigh the small security risk, but you must weigh the cost and benefits within your own environment.


How to grant this right/permission (tested on Windows 2008) to each instance of SQL Server:

·        Run lusrmgr.msc on the server to find the appropriate group name for each instance of SQL Server. For example: SQLServer2005MSSQLUser$SERENITYHOME$KAYLEE (SQL 2005 named instance), SQLServerMSSQLUser$SerenityHome$WASH (SQL 2008 named instance), or SQLServerMSSQLUser$SerenityHome$MSSQLSERVER (SQL 2008 default instance).

·        Run secpol.msc on the server.

·        Under Security Settings on the left, go to Local Policies and under that to User Rights Assignment.

·        Under Policy on the right side, go to "Perform volume maintenance tasks" and double click on it

·        On the Local Security Setting tab click on the "Add User or Group" button

·        In "Select Users, Computers, or Group"

o   Click on "Locations" and choose either your local computer name (for local groups/standalone) or your domain (for domain groups/clusters)

o   Click on "Object Types" and check "Groups"

o   In "Enter the object names to select" enter your SQL Server group created by SQL setup (standalone) or your cluster domain group (for clusters).

o   Choose "OK"

·        Restart SQL Server


Note that if you grant the right directly to the current SQL Server service account rather than to the group, you will have to remember to grant the right again when you change the account used to start SQL Server. In general you should grant the right to the group rather than the current account (any new account should be in the standard group and inherit its permissions).


Adding the permission takes affect with a SQL Server restart, but removing the permission requires a reboot. Make sure you pay attention to what other groups have been granted this right. Often you will see local administrators having the permission and if you have the SQL Server service account in the local administrators group then it will have the permission through that group membership.


Once a SQL Server instance has this permission, SQL Server can take advantage of not having to zero out a file allocation if:

·        The file is not a log file

·         The OS supports the call to SetFileValidData function ( )

·        The account (directly or through group membership) has the privilege required

·        It is not a sparse file  ( a.k.a. Snapshots)

·        Transparent Data Encryption (TDE) is not enabled

·        Trace flag 1806 to disable instant file initialization is not on.


To see which files are being zeroed out, you can use the undocumented trace flag 3004 and the undocumented sp_readerrorlog. Undocumented means it is unsupported and may change or be removed with no notice at any time. Trace flag 3004 shows information about backups and file creations. Trace flag 3605 redirects the output to the SQL error log.


WARNING: These trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.


DBCC TRACEON(3004,3605,-1)




EXEC sp_readerrorlog




DBCC TRACEOFF(3004,3605,-1)


If Instant File Initialization is not enabled, then you will see SQL Server zeroing out both the mdf/ndf (data) and ldf (log) files. You may see a wait type of  PREEMPTIVE_OS_SETFILEVALIDDATA in sys.dm_exec_requests while the growth occurs.

2009-12-16 10:16:27.000              spid52   Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero.mdf from page 0 to 160 (0x0 to 0x140000)

2009-12-16 10:16:27.020              spid52   Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero.mdf

2009-12-16 10:16:27.190              spid52   Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF from page 0 to 63 (0x0 to 0x7e000)

2009-12-16 10:16:27.200              spid52   Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF

2009-12-16 10:16:27.720              spid52   Starting up database 'TestFileZero'.

2009-12-16 10:16:27.740              spid52   FixupLogTail(progress) zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF from 0x5000 to 0x6000.

2009-12-16 10:16:27.740              spid52   Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF from page 3 to 32 (0x6000 to 0x40000)

2009-12-16 10:16:27.740              spid52   Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\TestFileZero_log.LDF 


If Instant File Initialization is enabled, then you will see SQL Server zeroing out only the ldf (log) files.

2009-12-16 11:04:33.000              spid57   Zeroing d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF from page 0 to 72 (0x0 to 0x90000)

2009-12-16 11:04:33.000              spid57   Zeroing completed on d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF

2009-12-16 11:04:33.330              spid57   Starting up database 'TestFileZero'.

2009-12-16 11:04:33.340              spid57   FixupLogTail(progress) zeroing d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF from 0x5000 to 0x6000.

2009-12-16 11:04:33.340              spid57   Zeroing d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF from page 3 to 32 (0x6000 to 0x40000)

2009-12-16 11:04:33.340              spid57   Zeroing completed on d:\cases\MSSQL10.WASH\MSSQL\DATA\TestFileZero_log.LDF



·        Instant Initialization - What, Why and How?

·        Misconceptions around instant file initialization

·        Paul Randal's blog - category = Instant Initialization

·        Database File Initialization
“Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy. For more information about assigning user rights, see the Windows documentation.”


-- Cindy Gross and Denzil Ribeiro

Comments (16)
  1. Jason Yousef says:

    Great work and article, keep it up….Thanks

  2. Wil Sisney (@HanSQL) says:

    This article has been very helpful.  Bonus points to the author for using Firefly/Serenity examples in their instance names.

  3. Taking a peek at SQL RAP recommendations-Instant File Initialization

  4. Jerry says:

    How to do this on SQL 2012? My instance runs under "NT ServiceMSSQLSERVER" which I cannot find. BTW, Kaylee is my favorite.

  5. MarcosLRosa says:

    Thanks for this post mate! I needed to get some rich info as this one.

  6. mergertech says:

    Really helpful information thanks  for sharing

  7. Thanks for the Feedback, glad you enjoyed the article

  8. Rob Nicholson says:

    I have created a Connect Item to make it easier to check whether Instant File Initialization is enabled on your instance:…/809901

  9. Lipod says:

    Another reason to enable Instant File initialization is if the operation fails with Msg 3013 when attempting to restore large database backups from Azure Blob storage.  Enabling Instant File Initialization appears to solve the problem.

  10. Shanky_621 says:

    How about tempDB.Instant file initilization works differently for Tempdb log files.

  11. Bill says:


    The NT ServiceMSSQLServer account is on the local machine, had you changed the location to the machine itself? I am thinking you had the location set to the domain, if so you will not be able to find that user, you must change the location to the computer then it will find it

  12. Simon says:

    I've been doing a series of tests on instant file initialisation on database recovery. I found that recovery completes in approaching half the time with it enabled:…/how-to-halve-your-database-recovery-time-in-60-seconds


  13. Anonymous says:

    This is a life saver!!! I found this (zero initialization) when I set up my SQL Server 2+ years ago and it makes creation/restoration of DB's (especially large ones) very FAST!!!! I had to make a copy of our 2 GIG accounting software DB and it completed in less than a minute. Not sure how long it would have taken without it, but the rep from the accounting software company told me before I created it that I should create a backup and call him back… when it completed 20 seconds later he was convinced that an error occurred as (in his words) "there was no way it could complete that quickly". According to it's about 85% quicker on a 1 GB file… the bigger the file the better the speed improvement..

  14. Anonymous says:

    Jerry is you search nt servicemssqlserver at the place of mssqlserver it should work. and change your search location to computer before doing that. hope it will help someone

  15. Tara Kant says:

    This article has been very helpful….

  16. CJ Gray says:

    Excellent article. Too bad it won’t print, at least under Windows 10 using IE11 nor Chrome 🙁

Comments are closed.

Skip to main content