SQL Server misunderstanding: 3rd-Day Instant file initialization can be enabled and disabled on SQL Server

This series of articles I saw in the sqlskill.com's PAUL blog, many misunderstandings are more typical and representative, the original from the T-SQL Tuesday #11: Misconceptions about... EVERYTHING !!, Our team has translated and organized the documents and published them on AgileSharp and blog. Hope to help you.

Misunderstanding #3: instant file initialization can be enabled in SQL Server a and disabled in SQL Server B.

A) is not allowed B) is allowed

Instant file Initialization is a little-known feature of SQL Server 2005 and later versions. This feature allows data files (only data files, not log files) to be initialized without entering 0. This method is a good way to greatly reduce Downtime in the event of a disaster-the restoration process starts directly when the database is restored, because the initialization process is not required.

I have already written an article about the misunderstanding of instant file initialization (see Misconceptions around instant initialization), but this does not mention this misunderstanding.

You cannot enable this feature in SQL Server. When SQL Server is started, it checks whether the account that starts SQL Server has the appropriate Windows permissions (that is, the "execute volume maintenance task" permission ), when the account that starts the SQL Server instance has this permission, this feature will be enabled for this instance, as shown in figure 1. kimberly has an article about How to enable this feature, Instant Initialization-What, Why, and How.

Figure 1. Enable the Volume Maintenance task (Perform Volume Maintenance Tasks)

You can check whether the instant file initialization feature starts in SQL Server. You can create a database with the tracking mark 3004 (3605 can force the output of error information) and check whether there is any Zero Filling Operation in the log, if the instant file initialization field is set to 0, this feature is not enabled in SQL Server.

In SQL Server, you can set trace flag 1806 to ON to temporarily stop the instant file initialization feature. If you want to permanently disable this feature, delete the "execute volume maintenance task" permission in the SQL Server account.

These two tracing marks were first mentioned in the SQL Server Premier Field Engineer Blog and How and Why to Enable Instant File Initialization blogs.

If possible, try to enable this feature.