question

GREGLARSEN-2839 avatar image
0 Votes"
GREGLARSEN-2839 asked ErlandSommarskog commented

Enableing Instant file initialization using SQL Server Management Console

I see that I'm able to enable and disable instance file initialization using "Instant File Initialization" item under the "Advanced" tab using SQL Server Configuration Manager. I have SQL Server 2019 installed. What I'm trying to figure out in what version of SQL Server did this become available. I am wonder if was SQL Server 2016, since that was the version that let you enable instant file initialization during the installation of SQL Server. Was it SQL Server 2016 when the added that options to configuration manager? If not when did Microsoft add this feature?

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Opps, that title should have read "Enabling Instant File Initialization using SQL Server Configuration Manager".

0 Votes 0 ·
GREGLARSEN-2839 avatar image
0 Votes"
GREGLARSEN-2839 answered ErlandSommarskog commented

Erland, nice hearing from you. If I understand your post you tested the different versions of SQL Server Configuration Manager (SSCM) and found that only the SQL Server 2019 version support enabling/disabling Instant File Initialization on the "Advanced" tab. In all other versions you need to use secpol.msc to enable instant file initialization after SQL Server has been installed. Of course starting with SQL Server 2016 you can also set up instant file initialization during the installation process. Please confirm that is what you found during testing of SSCM.

Thanks for testing this for me.

Greg

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, that's only SSCM 2019. With older versions of SQL server, you need to go to the regular Windows place.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

Instant File Initialization has existed since at least SQL 2014. I believe it was introduced in SQL 2008.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GREGLARSEN-2839 avatar image
0 Votes"
GREGLARSEN-2839 answered

Actually it is my understanding that instant file initialization has been around since 2005. But in those days it had to be installed by manually giving the account running sql server volume management permissions. My question is, when did Microsoft make it easy to enable by setting the "Instant File Initialization" setting to "Yes" via that "Advanced" tab using SQL Server Management Console? Hope that clarifies what I'm really asking.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

Hello Greg, it has been a while!

I have complete collection of SSCM from 2005 to 2019, so I could easily investigate this. This option (as the option for Lock Pages in Memory), is only available in SSCM 2019.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered GREGLARSEN-2839 commented

Hi Greg,

As mentioned by Erland, I only saw this option in the SQL Server 2019 Configuration Manager (right click SQL Server instance>properties>advanced).

In fact, I did not have an environment where all versions of SQL Server Configuration Manager were installed, but I tested SQL Server 2016&2017&2019 Configuration Manager. I think this is a new feature added from SQL Server 2019 Configuration Manager.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for confirming this is just a SQL Server configuration manager 2019 feature.

0 Votes 0 ·