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.

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

sql-2016-setup-server-configuration

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…

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s