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)
Thank you for this helpful article, Mr. Dave. Although, I have a question. I’m working as MSSQL DBA in a mid-range company.
We have many SQL servers, and we use domain accounts to run the MSSQL Engine Service. We recently had an workshop with a MSSQL expert, who recommended that we should put the domain account for MSSQL Engine under Local Policy called ” Perform Volume Maintenance Tasks”.
We always check for “Grant Perform Volume Maintenance Tasks for MSSQL Engine” during the installation, hence the NT SERVICE \MSSQLSERVER is already granted this right, when I check the Local Policies.
So my question is, is it enough to have granted the NT SERVICE\MSSQSERVER “Perform Volume Maintenance Tasks”, or should also the domain account (that runs the MSSQL Server Engine Service” also have the right?
Hi Dave! when i set Instant File Initialization in SQL Server 2019 Configuration Manager and press OK i have error: Not All privileges or groups referenced are assigned to the caller [0x80070514]
how to fix it?