Tuesday, 21 September 2010

Instant File Initialization – Is it Turned On?

I have posted a short note on instant file initialization previously, that post can be found here. The crux of enabling instant file initialisation is to give the service account running the SQL Service permission on the 'Perform Volume Maintenance Task' This is just a little follow up post to demonstrate how you can check that Instant File Initialisation is enabled. And I will show you how to temporarily disable it too.
In order to check for Instant File Initialization being enabled we need to turn on two undocumented trace flags, these output the result of a CREATE database statement to the log. If Instant File Initialization is enabled then you will see the log file being zeroed out. If it is not enabled you will see both the log and data files being initialised.


You will see in the error log I currently I have instant file initialization enabled and as such I only have entries in the SQL Server log for the log file being initialized.


You can temporarily disable Instant file initialization, to do this you need to turn on trace flag 1806.


If I then drop and recreate the IFI database.


We should then see both the ldf and mdf files being initialised in the log:


The 1806 trace flag is only a temporary measure if you wish to disable Instant File Initialisation then you need to remove the 'Perform Volume Maintenance Tasks' right from the service account and the SQL Service restarted.

Paul Randal (blog | Twitter) recommends that you enable Instant File Initialization if at all possible.

Edit: 21 Sept 2010 to correct typos

No comments:

Post a Comment

Featured post

SQL Server 2008 and SQL Server 2008 R2 - OUT of SUPPORT July 2019

Both SQL Server 2008 and SQL Server 2008 R2 go out of extended support with Microsoft in July 2019. That’s fast approaching and as we enter ...