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.
- SQL SERVER – How to Turn On / Enable Instant File Initialization?
- How to Check Status of Instant File Initialization? – Interview Question of the Week #184
- SQL SERVER – How to Check If Instant File Initialization Enabled or Not?
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.
All you need is to use SQL Server 2019 Configuration Manager.
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.
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)
