What You know about SQL Server Instant File Initialization (IFI)?

Mustafa EL-Masry Mustafa EL-Masry

Mustafa EL-Masry

Senior Database Consultant | Performance Tuning | ? Azure | MCSA | MCSE | MCT | Author

发布日期: 2020年2月9日
+ 关注
  1. Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by zeroing the files (filling with zeros) when you perform one of the following operations (Create DB, add data or log file to DB, increase the size for DB File, Restore DB or Filegroup
  2. Instant file initialization will avoid the SQL Server for writing zero in this operation
  3. Instant file initialization allows for fast execution of the previously mentioned file operations
  4. Instant file initialization reclaims used disk space without filling that space with zeros
  5. Instant file initialization is available only for data files. Log files will always be zeroed when being created, or growing in size
  6. Disabling instant file initialization will increase allocation times for data files
  7. Instant file initialization not working with Transparent Data Encryption (TDE)
  8. Starting with SQL Server 2016 (13.x), this permission can be granted to the service account at install time, during setup
  9. Starting with SQL Server 2012 (11.x) SP4, and SQL Server 2016 (13.x) SP1 and later, the column instant_file_initialization_enabled in the sys.dm_server_services DMV can be used to identify if instant file initialization is enabled.
  10. Instant file initialization is available only on MicrosoftWindows XP Professional or Windows Server 2003 or later versions
  11. Instant file initialization is only available if the SQL Server service startup account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group
  12. If the SQL Server service startup account has not been granted SE_MANAGE_VOLUME_NAME we will find this massage in SQL Server error log (Database Instant File Initialization: disabled)
  13. To Enable it secpol.msc (Local Security Policy) > Expand the Local Policies Folder > Click on User Rights Assignment > Go down to the “Perform Volume Maintenance Tasks” option and double click it > Add your SQL Server Service account, and click OK out of the dialog
  14. Enable Instant file initialization Required SQL Server Restart

要查看或添加评论，请登录

Mustafa EL-Masry的更多文章

社区洞察

其他会员也浏览了