SQL Server / Instant File Initialization

Posted by Barac in SQL Server, SQL Tips and Tricks on Dec 11th, 2017 | 0 comments

Instant file initialization reclaims used disk space without filling that space with zeros. What those this means for our SQL Server data files?

Data file grow needs to be completed immediately.

Lets do a simple test.

I will create additional tempdb files with fixed 10GB size without Instant file initialization enabled for the SQL Server service account.

/* Adding additional tempdb files */
USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'D:\MSSQL\TempDB\tempdev2.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'D:\MSSQL\TempDB\tempdev3.ndf' , SIZE = 10GB , FILEGROWTH = 0);
GO

This command was finished within 50 seconds

Now, if we remove those tempdb files

/* Remove additional tempdb files */
USE [master];
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
GO

And recreate those tempdb data files again with previously granted permissions to the SQL Server service account to perform volume maintenance tasks.

To do that we need to go to the Local Security Policy.

Then recreate tempdb files again.

/* Adding additional tempdb files */
USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'D:\MSSQL\TempDB\tempdev2.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'D:\MSSQL\TempDB\tempdev3.ndf' , SIZE = 10GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev4', FILENAME = N'D:\MSSQL\TempDB\tempdev4.ndf' , SIZE = 10GB , FILEGROWTH = 0);
GO

You can see now that SQL Server created the new tempdb data files in matter of second

If you are using SQL Server 2016 or higher version and you want to enable instant file initialization, you will have a checkbox option during the installation of SQL Server.

Simple, if you want to enable Instant File Initialization to the SQL Server Engine Service Account, all you need is to check that checkbox, bare in mind that this option is unchecked by default.

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / Instant File Initialization

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud