Instant file initialization

Get the instant file initialization status using Powershell

TSQL:

SELECT @@SERVERNAME AS [Server Name] ,
— RIGHT(@@version, LEN(@@version) – 3 – CHARINDEX(‘ ON ‘, @@VERSION)) AS [OS Info] ,
— LEFT(@@VERSION, CHARINDEX(‘-‘, @@VERSION) – 2) + ‘ ‘
— + CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR(300)) AS [SQL Server Version] ,
service_account ,
instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename LIKE ‘SQL Server (%’

  1. save above Tsql as ifiq.sql
  2. save the server names in a text file as serverlist.txt
  3. make sure all paths are updated and run below script in powershell

$servers = Get-Content “C:\Users\Desktop\serverlist.txt”

$output =foreach ($ser in $servers)
{
Invoke-Sqlcmd -inputfile “C:\Users\Desktop\ifiq.sql” -serverinstance $ser

}

$output | format-table |out-file -FilePath “C:\Users\Desktop\ifiq.txt” -Append

How to Enable Instant File Initialization – SQL Server

Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:
1) Create a database.
2) Add files, log or data, to an existing database.
3) Increase the size of an existing file (including autogrow operations).
4) Restore a database or filegroup.

Instant file initialization reclaims used disk space without filling that space with zeros.
This will help data files initialization instantaneously.

Steps to Enable Instant file initialization :

ifi1

ifi2

Go to Administrative tools –> Local security policy –> under local policies … select user Rights Assignment –>select …perform volume maintenance tasks –> properties — > add your SQL Server service account.

This will made database creations, file growths and file creations fast.