Sql Server Instant File Initialization

Sql Server Instant File Initialization is a Sql Server option that, I have to admit, I didn’t know. Activating this option you can slightly improve performances when Sql Server needs to grow data files and during backup and restore of a database.

When Sql Server grows data files and transaction log files, it fills with zeros the newly allocated space. This could be a time consuming job, and especially in very busy Sql Server instances could lead to waits because no session could write to the growing file while this operation is in progress.

But, at least for data files, you can disable this behavior (sorry, transaction log files are always zeroed…).

How can you activate Instant File Initialization?

Simply follow these instructions….

First of all check the credentials under which the Sql Server service is running. So open the Windows services list and double click on “Sql Server” service.

In my case the user is “NT Service\MSSQLSERVER”.

Now click Windows Key + R, to open the “Run” window, and execute “secpol.msc”.

The “Local Security Police” windows will appear. In the left pane, select the item “Security setting\Local policies\User Right Assignment”.

On the right panel double click the item “Perform volume maintenance tasks” and add the user used to run the Sql Server service.

The following screenshot should clarify:

Here is the screenshot with the user list updated.

At this point you have to restart the Sql Server Service.

To check if Sql Server is zeroing data files, execute the following code:

dbcc traceon(3004,3605,-1) 
go 

create database TrashMe 
go 

exec sp_readerrorlog 
go 

drop database TrashMe 
go 

dbcc traceoff(3004,3605,-1) 
go

The output of the “sp_readerrorlog” should log something similar about zeroing log files:

Zeroing C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TrashMe_log.ldf from page 0 to 1024 (0x0 to 0x800000)
Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TrashMe_log.ldf (elapsed = 5 ms)

But you will not find any similar about data files, so you can conclude that Instant File Initialization is working. Remember that from Sql Server 2016 you can activate Instant File Initialization during setup, checking the option “Grant Perform Volume Maintenance Task to Sql Server Database Engine Service”.

Just a little note before the greetings: there is a security risk associated with instant file initialization.

I suggest you to read more at:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15

Related Posts