0 Instant file initialization ( IFI )

SQL Server can take advantage of IFI to reduce the time that needs for SQL Server to grow the data base file.

To enable this, we need to grant the “perform volume maintenance task” under gpedit

DBCC TRACEON(3004,3605,-1);

DBCC TRACESTATUS;

CREATE DATABASE TestDB;

EXEC sp_readerrorlog;

DROP DATABASE TestDB;

DBCC TRACEOFF(3004,3605,-1);

We only see sql server is zeroing out the log file

This apply to the tempdb as well.

Other scenario that will not instant initialization the file:

1. Remove the permission from security policy

2. Use trace flag 1806

3. Turn on the TDE

USE master;

GO

DROP DATABASE TestDB

GO

DBCC TRACESTATUS

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Summer2010';

GO

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TestCertificate';

GO

CREATE DATABASE TestDB

GO

USE TestDB

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE TestDB SET ENCRYPTION ON;

GO 

ALTER DATABASE TestDB

MODIFY FILE(NAME = 'TestDB' , SIZE = 200MB)

EXEC sp_readerrorlog;

4.Create database snapshot

CREATE Database TestDBSS

ON

(Name= 'TestDB' , Filename =  'C:\TestDBSS.ss')

AS SNAPSHOT OF [TestDB]

EXEC sp_readerrorlog;

5. DBCC CHECKDB

dbcc checkdb(TestDB)

Reference 

http://social.technet.microsoft.com/wiki/contents/articles/23269.sql-server-instant-file-initialization-exceptions.aspx