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)
0 comments:
Post a Comment