SQL Server misunderstanding 30th about 3rd day the Instant file initialization feature can be turned on and off in SQL Server _mssql

This series of articles is I sqlskill.com Paul's blog to see, many misunderstandings are more typical and representative, the original from T-SQL Tuesday #11: Misconceptions about .... EVERYTHING!!, through our team's translation and collation published in Agilesharp and blog Park. Hope to help you.

Myth #3: Instant file initialization features can be turned on and b in SQL Server

A) is not allowed B is allowed

Instant file initialization is a little-known feature of the version on SQL Server 2005 and above. This feature allows the process of initializing a data file (just a data file, not including a log file) to skip filling in the 0 initialization process. This approach is a good way to drastically reduce downtime in the event of a disaster---recover the database by eliminating the process of filling in 0 initialization and starting the recovery process directly.

I have written about the misunderstanding of instant file initialization (see misconceptions around instant initialization), but this does not refer to this area of misunderstanding.

You are not able to open this feature in SQL Server. When SQL Server starts, it checks to see if the account that started SQL Server has the appropriate Windows permissions (that is, "Perform volume maintenance Tasks"), and when the account that starts the SQL Server instance has this permission, this feature is opened for this instance, as shown in Figure 1. Kimberly has an article on how to open this feature detail instant initialization-what, Why, and how.

Figure 1. Turn on the Perform Volume maintenance task (perform Volume maintenance tasks)

You can see in SQL Server whether the Instant file initialization feature starts by tracking flag 3004 (3605 can force output error messages) to create a database, see if there are 0 operations in the log, and if the instant file initialization has a 0 initialization operation, this feature is in the SQL The server is not open.

You can temporarily stop the instant file initialization feature by setting trace flag 1806 to on in SQL Server. If you want to permanently prohibit this feature, please remove the "Perform Volume maintenance task" permission in the Startup SQL Server account.

These two trace flags were first mentioned in the two posts in the SQL Server Premier Field Engineer Blog and how and Why to Enable Instant File initialization.

If you can, try to open this feature.