SQL SERVER – How to Turn On / Enable Instant File Initialization?

This blog post is a follow-up blog post on the subject of Instant File Initialization (IFI). I suggest you read the following blog posts before you continuing this blog post. Today we will learn how to Turn On / Enable Instant File Initialization?

• How to Check Status of IFI? – Interview Question of the Week #184
• SQL SERVER – How to Check If IFI Enabled or Not?

In today’s blog post we will learn how we can enable Instant File Initialization if it is disabled for your server. The method which I am going to explain you works for pretty much all the versions of SQL Server.

Just so you know that SQL Server can’t enable IFI, it has to be enabled in Windows Security Policy. Here are the steps for the same.

• First, go to Start Menu and run secpol.msc to bring up Local Security Policy
• Expand the Local Policies Folder
• Click on User Rights Assignment
• Go to the Perform Volume Maintenance Tasks”” option and double click it
• Add your SQL Server Service account, and click OK
• Restart your SQL Server services (MUST DO)

If you add incorrect service account or forget to restart your SQL Server services, you will not see the impact of IFI on your server.

It would be interesting to know if you keep IFI on for your server or not. Please leave a comment.

Reference: Pinal Dave (https://blog.SQLAuthority.com)