Are you trying to enable Instant File Initialization in SQL server?

This guide will help you.

In SQL Server, instant file initialization (IFI) allows for faster execution of the previously mentioned file operations, since it reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform SQL related queries.

In this context, we shall look into how to enable Instant File Initialization (IFI) in SQL Server.

More about Instant File Initialization in SQL Server ?

SQL Server allocates space and fills it with zeroes for certain operations like creating/restoring a database or growing data/log files which is tedious work.

With Instant File Initialization (IFI), we can skip the step of zero-writing and begin using the allocated space immediately for data files. Moreover, IFI allows for faster execution of the file operations in SQL Server.

Instant file initialization (IFI) allows SQL Server to skip the zero-writing step and begin using the allocated space immediately for data files. It doesn't impact growths of your transaction log files, those still need all the zeroes.

How to Enable Instant File Initialization in SQL Server ?

Here, you will learn how to enable IFI.

Instant file initialization is only available if the SQL Server service startup account has been granted SE_MANAGE_VOLUME_NAME.

Members of the Windows Administrator group have this right. Also, they can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

Here are the steps that we follow to grant an account the Perform volume maintenance tasks permission:

1. On the computer where we create the data file, we open the Local Security Policy application (secpol.msc).

2. Next, in the left pane, we expand Local Policies and then click User Rights Assignment.

3. Then in the right pane, we double-click Perform volume maintenance tasks.

4. Now, we click Add User or Group and add the account that runs the SQL Server service.

5. We click Apply, and then close all Local Security Policy dialog boxes.

6. Finally, we restart the SQL Server service and check the SQL Server error log at startup.

Security considerations with Instant File Initialization:

i. We recommend enabling IFI as its benefits can outweigh the security risk.

ii. When using IFI, the deleted disk content is overwritten only if new data is written to the files. For this reason, the deleted content can be accessible by an unauthorized principal, until some other data writes on that specific area of the data file.

iii. While the database file is attached to the instance of SQL Server, the disclosure risk of the information is reduced by the discretionary access control list (DACL) on the file.

This DACL allows file access only to the SQL Server service account and the local administrator. However, when the file detaches, it can be accessible by a user or service that does not have SE_MANAGE_VOLUME_NAME.

SE_MANAGE_VOLUME_NAME user right

We can assign the SE_MANAGE_VOLUME_NAME user privilege in Windows Administrative Tools, Local Security Policy applet. Under Local Policies select User Right Assignment and modify the Perform volume maintenance tasks property.

Performance considerations

The Database File initialization process writes zeros to the new regions of the file under initialization. The duration of this process depends on the size of the file portion that we initialize, the response time, and the capacity of the storage system.

If the initialization takes a long time, we see the following messages recorded in the SQL Server Errorlog and the Application Log.

Msg 5144

Autogrow of file '%.*ls' in database '%.*ls' was cancelled by user or timed out after %d milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Msg 5145

Autogrow of file '%.*ls' in database '%.*ls' took %d milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

A long Autogrow of a database and/or transaction log file may cause query performance problems. Because an operation that requires the autogrowth of a file will hold on to resources such as locks or latches during the duration of the file grow operation. Also, we see long waits on latches for allocation pages. The operation that requires the long autogrow will show a wait type of PREEMPTIVE_OS_WRITEFILEGATHER.

[Need urgent assistance with SQL queries? – We'll help you. ]