Script to verify if "Instant File Initialization" is Enabled

Instant file initialization is a wonderful feature of the Windows file system - not of SQL Server, please note -- that allows the server to allocate disk pages without having to set each bit to zero. You can imagine the time savings when SQL Server is, say, adding a GB to a data file. Note that because of the way they embedded information in the first few bytes of the actual pages of the log files themselves - sigh - log files can't use Instant File Initialization. But still, it's a must-have (unless the idea of having old data laying around in your pages gives your security people the willies - best check with them). You can find lots of info about this all over the web, but here's a little script that will verify that you've jumped through all the hoops correctly.

--  Once you have added the SQL Server security account to this security policy 
--  you need to then execute gpupdate /force from the command prompt and restart 
--  the SQL Service for this change to take effect.

USE master
SET NOCOUNT ON

-- If a database named DummyTestDB_VerifyIFI already exists drop it. 
IF DB_ID('DummyTestDB_VerifyIFI') IS NOT NULL
    DROP DATABASE DummyTestDB_VerifyIFI

-- Temp table to hold output from sp_readerrorlog. 
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL 
    DROP TABLE #SqlLogs

GO

CREATE TABLE #SqlLogs(LogDate datetime, 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_VerifyIFI 
GO

-- Turn off trace flags 3004 and 3605. 
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

-- Remove the dummy database. 
DROP DATABASE DummyTestDB_VerifyIFI

-- Now go check the output in the current SQL Server errorlog 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_VerifyIFI.mdf%'
              AND LogDate > DATEADD(HOUR, -1, LogDate))
    PRINT 'We do NOT have instant file initialization.'
ELSE
    PRINT 'We have instant file initialization.'