SQL Server / Instant File Initialization

Instant file initialization reclaims used disk space without filling that space with zeros. What those this means for our SQL Server data files?

Data file grow needs to be completed immediately.


Lets do a simple test.

I will create additional tempdb files with fixed 10GB size without Instant file initialization enabled for the SQL Server service account.


This command was finished within 50 seconds


Now, if we remove those tempdb files


And recreate those tempdb data files again with previously granted permissions to the SQL Server service account to perform volume maintenance tasks.

To do that we need to go to the Local Security Policy.



Then recreate tempdb files again.


You can see now that SQL Server created the new tempdb data files in matter of second


If you are using SQL Server 2016 or higher version and you want to enable instant file initialization, you will have a checkbox option during the installation of SQL Server.


Simple, if you want to enable Instant File Initialization to the SQL Server Engine Service Account, all you need is to check that checkbox, bare in mind that this option is unchecked by default.




Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *