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 (although there )

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.

DBCC TRACEON (3004)
DBCC TRACEON (3605)

CREATE DATABASE IFI

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.

DBCC TRACEON (1806)

If I then drop and recreate the IFI database.

DROP DATABASE IFI
CREATE DATABASE IFI

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.