Database Instant Initialization

Go to Run Command

Type gpedit.msc

A New dialog box will appear

1. Go to Windows Settings under Security Settings
2.Navigate the Local Policies and view the subfolder called user Rights Assignment
3.Select the Perform volume Maintenance Policy
4.Double Click , Go to the Properties add the SQL Service already running under user or Group
5.Ensure it has been added and restart the SQL Engine.

Log in to SSMS

1.Create the database with the sample size of 50 Gigabytes , it should be finished within 7 seconds instead of 12 to 15 Minutes.

Refer the Screenshot Below.

http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx


Check the IFI is Enabled or not :

USE MASTER;

SET NOCOUNT ON

-- *** WARNING: Undocumented commands used in this script !!! *** --

--Exit if a database named DummyTestDB exists

IF DB_ID('DummyTestDB') IS NOT NULL

BEGIN

  RAISERROR('A database named DummyTestDB already exists, exiting script', 20, 1) WITH LOG

END

--Temptable to hold output from sp_readerrorlog

IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL DROP TABLE #SqlLogs

GO

CREATE TABLE #SqlLogs(LogDate datetime2(0), ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))

--Turn on trace flags 3004 and 3605

DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS

--Create a dummy database to see the output in the SQL Server Errorlog

CREATE DATABASE DummyTestDB

GO

--Turn off trace flags 3004 and 3605

DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

--Remove the DummyDB

DROP DATABASE DummyTestDB;

--Now go check the output in the SQL Server Error Log File

--This can take a while if you have a large errorlog file

INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)

EXEC sp_readerrorlog 0, 1, 'Zeroing'

IF EXISTS(

           SELECT * FROM #SqlLogs

           WHERE TEXT LIKE 'Zeroing completed%'

            AND TEXT LIKE '%DummyTestDB.mdf%'

            AND LogDate > DATEADD(HOUR, -1, LogDate)

        )

   BEGIN

    PRINT 'We do NOT have instant file initialization.'

    PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'

   END

ELSE

   BEGIN

    PRINT 'We have instant file initialization.'

   END

or 

exec xp_cmdshell 'whoami /priv'

you will see something below 

SeManageVolumePrivilege         Perform volume maintenance tasks          Enabled