Tuesday, September 16, 2014

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

image

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

image

This apply to the tempdb as well.

image

image

Other scenario that will not instant initialization the file:

1. Remove the permission from security policy

2. Use trace flag 1806

image

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;

image

4.Create database snapshot

CREATE Database TestDBSS
ON
(Name= 'TestDB' , Filename =  'C:\TestDBSS.ss')
AS SNAPSHOT OF [TestDB]
EXEC sp_readerrorlog;

image

5. DBCC CHECKDB

dbcc checkdb(TestDB)

image

Reference 

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates