(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 errorlog) 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 MCM Cindy Gross (Twitter) and current-MCM-candidate Denzil Ribeiro – see their post How and Why to Enable Instant File Initialization.
If you're able to – turn this feature on!