(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
A bit of a shorter one for today as we’re flying home from Boston to Seattle – home for a week then off again to SQL Connections in Las Vegas. It’s all go…
Myth #3: Instant file initialization can be a) enabled and b) disabled from within SQL Server.
a) FALSE and b) TRUE, respectively.
Instant file initialization is a little-known feature of SQL Server 2005 onwards that allows data files (only, not log files) to skip the usual zero initialization process that takes place. It’s a fabulous way to reduce downtime when a disaster occurs and you have to restore a database from scratch – as the new data files that are created don’t spend (potentially) hours being zero’d before the actual restore operation can take place.
I’ve done a blog post about instant file initialization misconceptions before (see Misconceptions around instant initialization) but that didn’t cover this aspect of the feature.
You *cannot* enable it from within SQL Server. SQL Server does a one-time check at startup whether the SQL Server service account possesses the appropriate Windows permission (Perform Volume Maintenance Tasks a.k.a. SE_MANAGE_VOLUME_NAME) and then instant file initialization is enabled for that instance. Kimberly’s excellent blog post Instant Initialization – What, Why, and How has the details on how to enable the feature (and a lot more besides).
You *can* check from within SQL Server to see if it’s running. Enable trace flag 3004 (and 3605 to force the output to the error log) and then create a database. In the error log you’ll see messages indicating that the log file is being zero initialized. If instant file initialization is NOT enabled, you’ll also see messages about the data file being zero initialized.
You *can* disable instant file initialization from within SQL Server, albeit only temporarily. Turning on trace flag 1806 will disable instant file initialization while the trace flag is enabled. To turn it off permanently, you’ll need to remove the security permission from the SQL Server service account.
These two trace flags were first documented in the SQL Server Premier Field Engineer Blog by MCMs Cindy Gross (Twitter) and Denzil Ribeiro – see their post How and Why to Enable Instant File Initialization.
If you’re able to – turn this feature on!
5 thoughts on “A SQL Server DBA myth a day: (3/30) instant file initialization can be controlled from within SQL Server”
Hi Paul,
i have instant file initialized enable in my testing environment, i enabled trace flags 3004,3605 just to check instant file initialization enabled & its works as expected Zeroing only log files. however if i run DBCC CHECKDB or if i create database snapshot it Zeroing even datafiles, is this behavior expected or am i missing something here.
Thanks
Praveen D’sa
Yes, database snapshots do not use instant file initialization, but the data files are very small initially.
So below features doesnt use Instant file initialization
1) TDE
2) Snapshot
3) Filestream
4) Buffer pool extension file.
5) in memory oltp checkpoint files
Please correct me if not the same as above mentioned
Correct
If not have enabled IFI and we start restoring a DB and kill it instantly, it’ll also behave like IFI. means no need of zero initialize.