sqlskills-logo-2015-white.png

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.

Enjoy!

15 thoughts on “How to tell if you have instant initialization enabled?”

  1. I must say, I love instant initialization. It’s awesome. But I always wondered why it’s not available for the log file. I assume there’s a technical reason… but what is it? Does it depend on having the rest of the file be zeroed out? Doesn’t it already know where it’s start and stop points are anyways, since the log is circular?

    Reply

  2. Paul,

    Thanks for this post.

    Getting the list of privileges assigned to a certain user is useful for the reasons you described (checking the “Perform volume maintenance tasks” privilege – SeManageVolumePrivilege) as well as other reasons (such as confirming the “Lock pages in memory” privilege – SeLockMemoryPrivilege).

    I learned that WhoAmI can only show the privileges of the logged-on user. It can’t be asked to show the privileges of another user, process, or service. I have concerns with enabling xp_cmdshell, even for short time periods.

    After a bit of looking, I found a free alternative from Microsoft (Mark Russinovich – Sysinternals) called AccessChk (http://technet.microsoft.com/en-us/sysinternals/bb664922.aspx). It offers command line options to specify a user (-a), a service name (-c), or a process name (-p) – instead of only reporting on the logged-on user.

    As an example, I was able to run this command from a command window:

    AccessChk –p sqlservr.exe –f –q –v

    This command returned the service account used by the SQL Server instance and its assigned privileges – without having to enable xp_cmdshell!

    One minor difference between AccessChk and WhoAmI: AccessChk shows the privilege name (SeManageVolumePrivilege, etc.) and the state (enabled / disabled) for each privilege, while WhoAmI does that and adds a description column (Perform volume maintenance tasks, etc.). Most of the privilege names are fairly self-descriptive, so I don’t consider it a major gap. And with a relatively small number of privileges that exist, one could come up with a privilege name / description lookup pretty easily, if needed.

    I haven’t tried running AccessChk on servers with multiple active DB instances, but I suspect that the example command line above will return feedback on all instances (since it doesn’t use a path name for the process name, and since the process option also allows an * to show all processes). Let me know if you happen to try it that way.

    Thanks for sparking the interest in this topic. I enjoy learning new ways of being better informed about a given environment.

    Scott R.

    Reply

  3. For those of you who ran into even more security when tryin to enable the cmdshell, here is a very slightly modified script to enable advanced options as well, then turn them off afterwards:

    USE master
    GO
    EXEC sp_configure ‘show advanced options’, 1
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    EXEC sp_configure ‘xp_cmdshell’, 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO

    CREATE TABLE #xp_cmdshell_output (Output VARCHAR (8000));
    GO

    INSERT INTO #xp_cmdshell_output EXEC (‘xp_cmdshell ”whoami /priv”’);
    GO

    IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE ‘%SeManageVolumePrivilege%’)
    PRINT ‘Instant Initialization enabled’
    ELSE
    PRINT ‘Instant Initialization disabled’;
    GO

    DROP TABLE #xp_cmdshell_output;
    GO

    EXEC sp_configure ‘xp_cmdshell’, 0;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    EXEC sp_configure ‘show advanced options’, 0
    GO
    RECONFIGURE WITH OVERRIDE;
    GO

    Regards,Glenn

    Reply

  4. Paul, great post, but I do have a (hopefully useful) comment.

    Seems to me that query of the temp table needs to also include the condition text of ‘Enabled’ as well as containing the text ‘SeManageVolumePrivilege’ to indicate that the privilege is indeed held, as the results of ‘WHOAMI /PRIV’ will always return all privileges and their respective status for the current login.

    So this line:
    IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE ‘%SeManageVolumePrivilege%’)

    Should be:
    IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE ‘%SeManageVolumePrivilege%’ and Output LIKE ‘%Enabled%’)

    Otherwise you get a false positive. That is unless I haven’t gotten enough sleep (and that’s entirely possible ;) ).

    Thanks again!

    -Patrick

    Reply

  5. You can use it this way using context of SQL service account –

    execute as login=”
    exec xp_cmdshell ‘whoami /priv’

    Reply

  6. It is a lot simpler with SQL Server 2016: All you have to do is inspect the SQL Logs for “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.”
    So you could do something like this:
    exec xp_readerrorlog 0, 1, N’Database Instant File Initialization: enabled.’

    Reply

  8. Does instant file initialization work only for local disk files?
    I’ve tried mounting a CIFS network drive to my machine, when I create/expand db on the mounted drive, I see in the sql logs that it is zeroing the mdf file with the trace flags..

    it does not do this for databases created on the local disk.

    my problem is that local disk doesn’t have enough space to create the size of DB i want. would like some way to skip zeroing the file for the network drive as well.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

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.

Collaborate With Us
Choose a Course