{"id":934,"date":"2009-03-10T11:51:00","date_gmt":"2009-03-10T11:51:00","guid":{"rendered":"\/blogs\/paul\/post\/Misconceptions-around-instant-file-initialization.aspx"},"modified":"2017-04-13T09:51:47","modified_gmt":"2017-04-13T16:51:47","slug":"misconceptions-around-instant-file-initialization","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-instant-file-initialization\/","title":{"rendered":"Misconceptions around instant file initialization"},"content":{"rendered":"

Whenever I teach a class I’m amazed at the number of DBAs who don’t know about instant file initialization. Without wanting to redo blog posts that others have done, in a nutshell it allows a file to be create or grown ‘instantly’ by not having to zero the space in the file. This is especially useful in disaster recovery situations, where restoring a database may have to create the data files first – and with instant file initialization turned on it cuts out a bunch of downtime. See Kimberly’s blog post <\/span>Instant Initialization – What, Why and How?<\/span><\/a>\u00a0for more details.<\/span><\/p>\n

Anyway, the reason I’m posting today is to clear up some misconceptions that I keep hearing.<\/span><\/p>\n

Misconception: instant file initialization is on by default.<\/strong> No it isn’t. There’s a tiny security risk with having it enabled (for systems where the SQL data drives are shared by sensitive non-SQL files) so it’s off by default and requires granting the SE_MANAGE_VOLUME_NAME permission (more commonly known as Perform Volume Maintenance Tasks). Kimberly’s blog post explains how to enable it.<\/span><\/span><\/p>\n

Misconception: instant file initialization is Enterprise-only.<\/strong> No it isn’t.<\/span><\/span><\/p>\n

Misconception: instant file initialization applies to log files too.<\/strong> No it doesn’t. Log files cannot be instant initializated – my blog post <\/span><\/span>Search Engine Q&A #24: Why can’t the transaction log use instant initialization?<\/span><\/a>\u00a0explains why not.<\/span><\/p>\n

Misconception: NTFS performs the zeroing when instant file initialization is not enabled.<\/strong> No it doesn’t. SQL Server does it, because it can do it faster than NTFS. When instant file initialization is enabled, SQL skips zeroing the file and instead just calls the\u00a0Windows API SetFileValidData. The special permission is required to be able to call that API. MSDN has more details at <\/span><\/span>http:\/\/msdn.microsoft.com\/en-us\/library\/aa365544(VS.85).aspx<\/span><\/a>. Ok – that took a bit of remembering, and digging around on MSDN.<\/span><\/p>\n

Misconception: when instant file initialization is enabled, pages are zero’d before they’re written.<\/strong> No they’re not. An entire 8k image is written to disk, which overwrites that uninitialized 8k portion of the file. Another flavor of this misconception is that if a page is allocated from an extent in an instant initialized file, the unallocated pages in the extent are zero’d out – that’s not true either.<\/span><\/span><\/p>\n

Hope this helps<\/span><\/p>\n

PS And a perfect opportunity for another survey:<\/span><\/p>\n