What is ‘File Initialization’ ? When the database data files talk to the disk, they have to be ‘initialized’. This means that any newly allocated space is overwritten with zero values (0×0). Windows does this to avoid problems by accessing old data, previously stored on the same disk. This zero File Initialization causes operations like this to take much longer:
- Create new database
- Auto-growth of any data file
- Backup or restore
Ok. So what is ‘Instant File Initialization’ ? This is a Windows setting that will allow SQL Server to skip the zero initialization. Instant File Initialization will claim the space to be used WITHOUT pre-filling it with zeros. The impact? This setting will make data file growth faster. Much faster.
Instant File Initialization
If you don’t want that SQL Server is doing the zero-initialization of your data files, you can reconfigure SQL Server. If you grant the service account, under which SQL Server is running – the privilege Performance Volume Maintenance Task, SQL Server will skip the zero-initialization of the data files, if you have restarted SQL Server afterwards. As I have said this only applies to data files – log files are ALWAYS zero-initialized in SQL Server! There is NO WAY around that!!! Without the zero-initialization of the log file, the crash recovery process would have no idea where to stop, when the log file was wrapped around. Crash Recovery stops where it finds zero values in the header of the next log record to be processed.
You can grant the permission Performance Volume Maintenance Task through secpol.msc to the service account of SQL Server.
Start / Run – type in ‘secpol.msc’
Left side under Security Settings, go to Local Policies, then User Rights Assignment.
Right side under Policy, double-click ‘Perform volume maintenance tasks’.
In ‘Perform volume maintenance task Properties’, choose ‘Add User or Group’.
Add in your SQL Server Service account.
Restart your SQL Server service.
After a restart, SQL Server is now able to skip the zero-initialization of data files. When I’m running the CREATE DATABASE statement from about again, it takes around 250ms – that’s a huge difference!
If you want to know, if your SQL Server is running with this permission, or not, you can enable the trace flags 3004 and 3605 ( DBCCTRACEON(3605) ). With these trace flags enabled, SQL Server reports in the error log which files are zero initialized. When you afterwards create a new database, and the permission wasn’t granted to SQL Server, you can see from the error log, that data AND log files were zero-initialized:
If SQL Server has the permission Perform Volume Maintenance Task,you can see from the error log, that ONLY the log file was zero-initialized: