Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

Sql Server Instant File Initialization

Posted on December 20, 2020December 22, 2020 By

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.

Check Sql Server service user name

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

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

Run 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:

Add Sql Server user to the list

Here is the screenshot with the user list updated.

Sql Server user added

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

Sql Server, Sql Server 2016

Post navigation

Previous Post: One shot secret function: what’s that?
Next Post: Migrating Google GSuite mailboxes to Microsoft Office 365

Related Posts

Get all referencing tables of a table in Sql Server Sql Server
How to know who is blocking a query in Sql Server Sql Server
SQL Server LEN function return 0 passing a string with one space Sql Server
Sql Server error: Cannot construct data type date, some of the arguments have values which are not valid. Sql Server
Load a huge amount of data in a Sql Server table BCP
Sql Server: apply an aggregate function to fields values in a row Sql Server

Search

Related posts

  • Get SQL Server version in T-SQL
  • Reset Sql Server cache
  • How to know who is blocking a query in Sql Server
  • SQL Server and SARGables predicates

Categories

  • .NET 6 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (6)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (2)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2023 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark