SQL 2016 Grant Perform Volume Maintenance Task aka instant file initialization

Did I forget to check that box?

Instant File Initialization (IFI) has been around dating back to SQL 2005 and the requests to easily allow us to check this feature during Setup reach way back as well.

For the less SQL experienced IFI allows SQL server to make use of a file space right away instead of first overwriting it with zeros (zeroing it out). This is typically not a problem for smaller databases – there are on the other hand significant advantages/time saving available once a database has grown into the range of GB/TB numbers.

For readers familiar with tape backups – this is similar to waiting on a tape job erasing the entire tape (possibly hours)  vs. erasing just the header (seconds).

Some more information regarding IFI can be found here.

•  Instant Initialization – What, Why and How?  by Kimberly Tripp
• How and Why to Enable Instant File Initialization  by Cindy Gross and Denzil Ribeiro
• SQL Server: Instant File Initialization Exceptions

Back to the question at hand – did you forget to check the box below during SQL 2016 install?

Next a little excursion into SQL Server Managment Studio to allow us to confirm if you did.

Note: The query below assumes you have a directory structure of C:\MSSQL\DATAA\

and 300 MB drive space available for this test.

 

— Begin to copy here

— A little exercise to confirm “Grant Perform Volume Maintenance Task” privilege

— aka instant file initialisation has been successfully granted to your SQL Server.

— Did you forget to check the box during SQL Server 2016 install? 😉

— Additional SQL traceflag information can be found here

— https://msdn.microsoft.com/en-us/library/ms188396.aspx?f=255&MSPPError=-2147217396

— Traceflag 3004 and 3605 remain somewhat undocumented – just bing it 😉

USE master;

–Set Trace Flags 3004 and 3605 to On.

DBCC TRACEON(3004,-1); — additional information during Database creation etc.

DBCC TRACEON(3605,-1); — write events to the log instead of on screen

–Create a Test database to see what output is sent to the SQL Server Error Log

CREATE DATABASE MyTest ON PRIMARY

(NAME = N’MyTest’, FILENAME = N’C:\MSSQL\DATA\MyTest.mdf’, SIZE = 200MB)

LOG ON

( NAME = N’MyTest_log’, FILENAME = N’C:\MSSQL\DATA\MyTest.ldf’, SIZE = 100MB)

–Turn the two Trace Flags back OFF.

DBCC TRACEOFF(3004,3605,-1);

–Remove the Test database

DROP DATABASE MyTest;

–Check the output in the current SQL Server Error Log File

EXEC sp_readerrorlog 0,1,‘zeroing’

–For SQL 2016 this is so much easier 😉

EXEC sp_readerrorlog 0, 1,‘Instant File Initialization’

–The End

If you checked the box during SQL 2016 Setup you should see

• Database Instant File Initialization: enabled…