/*============================================================================
  File:     InstantFileInitialization.sql

  Summary:  This script shows the differences in file creation with and without
			fast file initialization.

  Date:     April 12, 2005

  SQL Server Version: 9.00.1116.08 (April CTP)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

DECLARE @StartTime	datetime
SELECT @StartTime = getdate()
CREATE DATABASE TestWithZeroInitialization
	ON PRIMARY 
	(NAME = N'TestWithZeroInitializationData', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.2\mssql\data\TestWithZeroInitializationData.MDF' , 
	SIZE = 2000,
	FILEGROWTH = 250,
	MAXSIZE = 3000)
LOG ON 
	(NAME = N'TestWithZeroInitializationLog', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.2\mssql\data\TestWithZeroInitializationLog.LDF' , 
	SIZE = 250, 
	FILEGROWTH = 50,
	MAXSIZE = 1000)
SELECT datediff(ms, @StartTime, getdate()) AS [TotalTIme With Zero Initilization]
go
-- Keep track of time here: 132810 (2:13)
-- Keep track of time here: 111216 (1:51)

--Grant permissions to the SQLService Account for 
-- Perform Volumn Maintenance Tasks
--Stop and restart SQL Server
select @@version
go

DECLARE @StartTime	datetime
SELECT @StartTime = getdate()
CREATE DATABASE TestWithoutZeroInitialization
	ON PRIMARY 
	(NAME = N'TestWithoutZeroInitializationData', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.2\mssql\data\TestWithoutZeroInitializationData.MDF' , 
	SIZE = 2000,
	FILEGROWTH = 250,
	MAXSIZE = 3000)
LOG ON 
	(NAME = N'TestWithoutZeroInitializationLog', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.2\mssql\data\TestWithoutZeroInitializationLog.LDF' , 
	SIZE = 250, 
	FILEGROWTH = 50,
	MAXSIZE = 1000)
SELECT datediff(ms, @StartTime, getdate()) AS [TotalTIme Without Zero Initilization]
go
-- Keep track of time here: 11046
-- Keep track of time here: 15390

-- Clean up the test databases to free up space
DROP DATABASE TestWithZeroInitialization
DROP DATABASE TestWithoutZeroInitialization