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

Learning never stops with SQL Server. In this blog, we would find new ways to find information. In this blog, we would see the current setting of instant file initialization and how to make the modification. Last year I wrote a few blogs about Instant File Initialization. Here is the link to them.

All methods described in the above blogs are still valid but there is a new way to check the same information without running query or checking the ERRORLOG file.

SQL SERVER 2019 - How to Turn On or Enable Instant File Initialization? fileinitialization-800x217

All you need is to use SQL Server 2019 Configuration Manager.

Solarwinds

SQL SERVER 2019 - How to Turn On or Enable Instant File Initialization? SSCM-2019-IFI-01

There is a drop-down which you can toggle and choose between Yes and No. Once we make the change to the value, SQL Server Configuration Manager makes the changes to security policy and grants the permission of “Perform Volume Maintenance Tasks” automatically. This was explained in my earlier blogs.

It was good learning for me that it can make changes to an earlier version of SQL Server also. In one of my lab servers, I have both SQL Server 2019 and SQL Server 2017 installed. I was able to make changes to setting for SQL Server 2017 instance and it was reflected.

SQL SERVER 2019 - How to Turn On or Enable Instant File Initialization? SSCM-2019-IFI-02

As you can see above NT SERVICE\MSSQL$SQL2017 (it’s a named instance called SQL2017) is added and trust me, I didn’t add it manually. I found that it is pretty cool stuff.

I found another interesting thing in the configuration manager and I would blog about that soon. I hope you have enabled this feature in your production server as it is one of the most important features to get the optimal performance. Follow me on twitter.

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

Solarwinds
, , ,
Previous Post
SQL SERVER – Performance and Recompiling Query – Summary
Next Post
Online Recorded Class – SQL Server Performance Tuning Practical Workshop

Related Posts

Leave a Reply

Menu