SYS-CON MEDIA Authors: Stackify Blog, Zakia Bouachraoui, Elizabeth White, Pat Romanski, Liz McMillan

Article

Instant File Initialization – Killer Feature for SQL Server

How to restore your backup fast in SQL Server 2016

The use of Instant File Initialization is a terrific way to reduce downtime during failure recovery. It won't take you much time to initialize files for filling them with zeros before the recovery. So keep in mind that there is such useful thing as Instant File Initialization.

When SQL Server reserves new space on a disk, it initializes the space with zeros. This behavior can be switched off thereby reducing the execution time of some operations and the load on the disk subsystem.

Disk space reservation without initialization is called Instant File Initialization. This feature is not widely known, although its use has become possible starting with SQL Server 2005.

The benefits of the Instant File Initialization are the following:

  1. Accelerating the creation of a new database.
  2. Reducing delays and time required to expand data files.
  3. Reducing the start time of SQL Server due to faster tempdb initialization.
  4. Reducing time for restoring from a backup, because SQL Server reserves space for files before restoration and then transfers the information from the backup into the files.

It is important to note that Instant File Initialization works only for data files (MDF and NDF). Log files(LDF) are always zero-initialized.

How to use Instant File Initialization?
The option may be enabled fairly simply. Open SQL Server Configuration Manager to see the name ofSQL Server instance.

After that, in the Local Security Policy window, navigate to User Rights Assignment and select Perform volume maintenance tasks.

On the Local Security Setting tab, add the server instance, as shown in the following picture.

SQL Server instance verifies the rights required for working with Instant File Initialization only once - during the launch. That is why you need to restart SQL Server to apply the configuration.

Test Cases
First, let's examine whether Instant File Initialization is enabled?

If it is disabled, upon execution of the query:

USE [master]
GO
IF DB_ID('IFI_DB') IS NOT NULL
DROP DATABASE IFI_DB
GO
DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS
CREATE DATABASE IFI_DB
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS
GO
IF DB_ID('IFI_DB') IS NOT NULL
DROP DATABASE IFI_DB
GO
EXEC sp_readerrorlog 0, 1

in SQL Server log, you will see that data files are filled with zeros:

Option disabled

But if Instant File Initialization is enabled, then only the log file is filled with zeros:

Option enabled

In case you need to temporarily disable Instant File Initialization, you can enable trace flag 1806. However, as practice shows, using this functionality greatly saves time and reduces disk load.

Here are a couple of test cases and time spent executing them with and without Instant File Initialization:

USE [master]
GO
IF DB_ID('IFI_DB') IS NOT NULL
DROP DATABASE [IFI_DB]
GO
CREATE DATABASE [IFI_DB]
CONTAINMENT = NONE
ON PRIMARY (NAME = N'IFI_DB', FILENAME = N'D:\DATABASES\SQL_2012\DATA\IFI_DB.mdf', SIZE = 102400MB
LOG ON (NAME = N'IFI_DB_log', FILENAME = N'D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf', SIZE = 2048KB)
GO
-- OFF: 00:16:04
-- ON:  00:00:12
ALTER DATABASE [IFI_DB] MODIFY FILE (NAME = N'IFI_DB', SIZE = 204800MB)
GO
-- OFF: 00:14:32
-- ON:  00:00:11
BACKUP DATABASE [IFI_DB]
TO DISK = N'D:\DATABASES\SQL_2012\BACKUP\IFI_DB.bak'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION
GO
IF DB_ID('IFI_DB') IS NOT NULL
DROP DATABASE [IFI_DB]
GO
-- OFF: 00:00:59
-- ON:  00:00:58
USE [master]
GO
RESTORE DATABASE [IFI_DB]
FROM DISK = N'D:\DATABASES\SQL_2012\BACKUP\IFI_DB.bak'
WITH FILE = 1, NOUNLOAD
-- OFF: 00:28:03
-- ON:  00:00:16

 

Summary
The use of Instant File Initialization is a terrific way to reduce downtime during failure recovery. It won't take you much time to initialize files for filling them with zeros before the recovery. So keep in mind that there is such useful thing as Instant File Initialization.

P.S: In SQL Server 2016, you can easily turn on this option upon installing a new instance.

More Stories By Jordan Sanders

Jordan Sanders is a Software Marketing Manager at Devart Company. He helps DBAs, software developers (C#, .NET, Delphi) from all around the globe to increase their productivity by using new tools, practices and new approaches to database development and management. He has experience in MySQL, SQL Server, Oracle databases consulting and also in Delphi development. He is always trying to share his knowledge and ideas with the community of his interest.

Latest Stories
As you know, enterprise IT conversation over the past year have often centered upon the open-source Kubernetes container orchestration system. In fact, Kubernetes has emerged as the key technology -- and even primary platform -- of cloud migrations for a wide variety of organizations. Kubernetes is critical to forward-looking enterprises that continue to push their IT infrastructures toward maximum functionality, scalability, and flexibility. As they do so, IT professionals are also embr...
As you know, enterprise IT conversation over the past year have often centered upon the open-source Kubernetes container orchestration system. In fact, Kubernetes has emerged as the key technology -- and even primary platform -- of cloud migrations for a wide variety of organizations. Kubernetes is critical to forward-looking enterprises that continue to push their IT infrastructures toward maximum functionality, scalability, and flexibility. As they do so, IT professionals are also embr...
The Japan External Trade Organization (JETRO) is a non-profit organization that provides business support services to companies expanding to Japan. With the support of JETRO's dedicated staff, clients can incorporate their business; receive visa, immigration, and HR support; find dedicated office space; identify local government subsidies; get tailored market studies; and more.
While a hybrid cloud can ease that transition, designing and deploy that hybrid cloud still offers challenges for organizations concerned about lack of available cloud skillsets within their organization. Managed service providers offer a unique opportunity to fill those gaps and get organizations of all sizes on a hybrid cloud that meets their comfort level, while delivering enhanced benefits for cost, efficiency, agility, mobility, and elasticity.
Kubernetes as a Container Platform is becoming a de facto for every enterprise. In my interactions with enterprises adopting container platform, I come across common questions: - How does application security work on this platform? What all do I need to secure? - How do I implement security in pipelines? - What about vulnerabilities discovered at a later point in time? - What are newer technologies like Istio Service Mesh bring to table?In this session, I will be addressing these commonly asked ...
The KCSP program is a pre-qualified tier of vetted service providers that offer Kubernetes support, consulting, professional services and training for organizations embarking on their Kubernetes journey. The KCSP program ensures that enterprises get the support they're looking for to roll out new applications more quickly and more efficiently than before, while feeling secure that there's a trusted and vetted partner that's available to support their production and operational needs.
Skeuomorphism usually means retaining existing design cues in something new that doesn’t actually need them. However, the concept of skeuomorphism can be thought of as relating more broadly to applying existing patterns to new technologies that, in fact, cry out for new approaches. In his session at DevOps Summit, Gordon Haff, Senior Cloud Strategy Marketing and Evangelism Manager at Red Hat, discussed why containers should be paired with new architectural practices such as microservices rathe...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It's clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Th...
xMatters helps enterprises prevent, manage and resolve IT incidents. xMatters industry-leading Service Availability platform prevents IT issues from becoming big business problems. Large enterprises, small workgroups, and innovative DevOps teams rely on its proactive issue resolution service to maintain operational visibility and control in today's highly-fragmented IT environment. xMatters provides toolchain integrations to hundreds of IT management, security and DevOps tools. xMatters is the ...
DevOps is under attack because developers don’t want to mess with infrastructure. They will happily own their code into production, but want to use platforms instead of raw automation. That’s changing the landscape that we understand as DevOps with both architecture concepts (CloudNative) and process redefinition (SRE). Rob Hirschfeld’s recent work in Kubernetes operations has led to the conclusion that containers and related platforms have changed the way we should be thinking about DevOps and...
With the rise of Docker, Kubernetes, and other container technologies, the growth of microservices has skyrocketed among dev teams looking to innovate on a faster release cycle. This has enabled teams to finally realize their DevOps goals to ship and iterate quickly in a continuous delivery model. Why containers are growing in popularity is no surprise — they’re extremely easy to spin up or down, but come with an unforeseen issue. However, without the right foresight, DevOps and IT teams may lo...
When you're operating multiple services in production, building out forensics tools such as monitoring and observability becomes essential. Unfortunately, it is a real challenge balancing priorities between building new features and tools to help pinpoint root causes. Linkerd provides many of the tools you need to tame the chaos of operating microservices in a cloud native world. Because Linkerd is a transparent proxy that runs alongside your application, there are no code changes required. I...
Kubernetes is an open source system for automating deployment, scaling, and management of containerized applications. Kubernetes was originally built by Google, leveraging years of experience with managing container workloads, and is now a Cloud Native Compute Foundation (CNCF) project. Kubernetes has been widely adopted by the community, supported on all major public and private cloud providers, and is gaining rapid adoption in enterprises. However, Kubernetes may seem intimidating and complex ...
Between the mockups and specs produced by analysts, and resulting applications built by developers, there exists a gulf where projects fail, costs spiral, and applications disappoint. Methodologies like Agile attempt to address this with intensified communication, with partial success but many limitations. In his session at @DevOpsSummit at 19th Cloud Expo, Charles Kendrick, CTO at Isomorphic Software, presented a revolutionary model enabled by new technologies. Learn how business and develop...
"There is a huge interest in Kubernetes. People are now starting to use Kubernetes and implement it," stated Sebastian Scheele, co-founder of Loodse, in this SYS-CON.tv interview at DevOps at 19th Cloud Expo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.