How to enable instant file initialization on SQL Server 2016 after installation?

Question

How to enable instant file initialization on SQL Server 2016 after installation?

biank64

SSC Enthusiast

Points: 178
More actions
April 29, 2020 at 7:16 pm

#3747443

Hello,
Is there any way to enable INI after SQL 2016 installation is done? It seems we can’t add the service account to the "Perform Volume Maintenance Task" policy anymore.
Can someone guide me if there's a way?
Many many thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

did you follow the instructions here https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15

biank64 SSC Enthusiast Points: 178 More actions · Answer 2

Yes, checked already but that only explains how to enable it at install time. Unfortunately, we missed to check the option and installation has been completed without it. This server is in production so looking for a way to enable it now.

Thank you!

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

you do need to learn how to read then - it is very clear the steps to do it post install.

To grant an account the Perform volume maintenance tasks permission:
On the computer where the data file will be created, open the Local Security Policy application (secpol.msc).
In the left pane, expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Perform volume maintenance tasks.
Click Add User or Group and add the account that runs the SQL Server service.
Click Apply, and then close all Local Security Policy dialog boxes.
Restart the SQL Server service.

biank64 SSC Enthusiast Points: 178 More actions · Answer 4

I guess I read it wrong. I'm not able to add the service account to "Perform Volume Maintenance Task" policy due to this:

"This setting is not compatible with computers running windows 2000 Service Pack 1 or earlier. Apply Group Policy objects containing this setting only to computers running a later version of the operating system"

Somehow, I was under the impression that this has to do with SQL 2016 but could be an AD issue. I'll check with AD guys on this.

Thanks much!

frederico_fonseca SSCoach Points: 16167 More actions · Answer 5

sometimes GUI does that - if you can get your hands on ntrights.exe (https://www.microsoft.com/en-us/download/details.aspx?id=17657 try using it

see https://littlekendra.com/2009/11/12/automating-sql-local-security-policy-rights-posh-and-ntrights/

biank64 SSC Enthusiast Points: 178 More actions · Answer 6

I don't think I can do that but will check with the AD team on that.

Again, thank you for your suggestion and help.